Get the Position of Second or Third of the Specified Character within a String

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))

Get the Position of Second or Third of the Specified Character1

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))

Get the Position of Second or Third of the Specified Character1


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])…
Related Posts

Find and Replace Multiple Values

This post will guide you how to find and replace multiple values at once with VBA macro or using formula  in Excel. How do I make multiple find and replace in Excel. Suppose that you have a few cells containing ...

Extract Last Two Words From Multiple Cells

Just assume that you have a few cells containing values/words and you want to extract the last two words from each cell into another separate cell; then you might think that it's not a big deal; because you would prefer ...

Extract Multiple Lines From A Cell

Suppose that you have listed some text in a single cell which is separated by the line break(you can do it by pressing ALT + ENTER after entering the text), and now you want to extract multiple lines of text ...

How to Remove All Asterisk Characters from Cells in Excel

Sometimes when we opening an excel spreadsheet to check data, we may notice that there are some asterisks exist in texts for security or other reasons. If you feel these special characters are invalid you can remove them from cells. ...

How to Convert Date Format from Dot MM.DD.YYYY to Slash MM/DD/YYYY in Excel

We often enter dates in excel tables, and we can enter current date by quickly press ‘Ctrl+;’ simultaneously. If in some cases we enter a date format that is not what we want, we have to switch the date format ...

Sidebar