In the previous post, we have talked that how to split text string by a specified character in excel and it need to get the position of the first specified character within a string. And this post will guide you how to get the position of the second or third specified character in a text.

Get the Position of Second of the Specified Character

To locate the position of the second or third of the specified character in a cell, you need to use the SUBSTITUTE function within the FIND function. You can refer to the following Excel formula:

=FIND("@",SUBSTITUTE(B1,"-","@",2))

Suppose that you want to get the position of the second dash character “-”in Cell B1, you need to use SUBSTITUTE function to replace the second dash character with new character “@”in Cell B1. Then using FIND function to search the specified character @ in the returned result of the SUBSTITUTE function. It will return the position of the second specified character “-“ in Cell B1.

 Get the Position of Third of the Specified Character

If you want to get the position of the third specified character in cell B1, than you can use the below formula:

=FIND("@",SUBSTITUTE(B1,"-","@",3))

Related Formulas

• Split Text String by Specified Character in Excel
  you should use the Left function in combination with FIND function to split the text string in excel. And we can refer to the below formula that will find the position of “-“in Cell B1 and extract all the characters to the left of the dash character “-“.=LEFT(B1,FIND(“-“,B1,1)-1).…
• Split Text String by Line Break in Excel
  When you want to split text string by line break in excel, you should use a combination of the LEFT, RIGHT, CHAR, LEN and FIND functions. The CHAR (10) function will return the line break character, the FIND function will use the returned value of the CHAR function as the first argument to locate the position of the line break character within the cell B1.…
• Split Text and Numbers in Excel
  If you want to split text and numbers, you can run the following excel formula that use the MIN function, FIND function and the LEN function within the LEFT function in excel. .…

Related Functions

• Excel Find function
  The Excel FIND function returns the position of the first text string (substring) from the first character of the second text string.The FIND function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the FIND function is as below:= FIND  (find_text, within_text,[start_num])…
• Excel SUBSTITUTE function
  The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE (text, old_text, new_text,[instance_num])…