23
Excel Formulas to Find the Position of 2nd & 3rd Instance of Character!!
By default, the FIND function returns the position number of the first instance of the character you are searching for. If you want the position number of the second instance, you can use the optional Start_Num argument. In this chapter, we will show the step-by-step procedure to find out the position of the 2nd and 3rd instance of a character in Excel. Let’s see them below!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel
- You can use the below formula to find out the position of the 2nd and 3rd instance of a character in Excel.
=FIND(“~”,SUBSTITUTE(text,char,”~”,instance))
- FIND – This function helps to find out the one text string inside the other. Read more on the FIND function.
- SUBSTITUTE – In Excel, the SUBSTITUTE Function can replace existing text with new text in a text string when you want to replace text based on its content, not position.
- Text – It represents the input value.
- Char – It specifies the character that you want to be found.
- Instance – It represents the position of the character.
- Comma symbol (,) – It is a separator that helps to separate a list of values.
- Parenthesis () – The main purpose of this symbol is to group the elements.
Let’s consider the below example image.
- Here we will enter the input names in Column B.
- Now we need to find out the Nth position of the character which is given in Column C and Column D.
- Select any cell and type the above-given formula.
- Finally, press the ** ENTER** key, you can get the result as shown below.
This page provides some useful tricks to find out the position of the 2nd and 3rd instance of a character in Excel. Hope you like it. If you have any questions or suggestions regarding this lesson, feel free to use the comments section below to let us know.
Thank you so much for visiting *Geek Excel!! **If you want to learn more helpful formulas, check out Excel Formulas *!! **
23