So far we have learned the simple steps to split the text string by a specified character in Excel. Here, we will guide you on how to get the position of the second or third specified character in a text. 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
Formulas and Explanations:
To locate the position of the second or third of the specified character within a text string, you need to use the FIND function along with the SUBSTITUTE function.
Get the Second Special Character Position:
=FIND(“@”,SUBSTITUTE(B1,”-“,”@”,2))
Get the Third Special Character Position:
=FIND(“@”,SUBSTITUTE(B1,”-“,”@”,3))
Syntax Explanation:
- FIND – In Excel, the FIND function will help to find out the one text string inside the other.
- SUBSTITUTE – It can replace existing text with new text in a text string when you want to replace text based on its content, not position. For more about the SUBSTITUTE function.
Steps to find the position of the second special character:
- Refer to the below example image.
- First, we will give the input text in Column B.
- Suppose you need to get the position of the second dash character “-” in Cell B3 , you need to use the above formula.
- In the formula, the SUBSTITUTE function helps to replace the second character with the new character “@” in the cell.
- Then, the FIND function will search the specified character @ in the returned result of the substitute function.
- Finally, it will count the position of the second specified character from the cell and return to the selected cell.
Steps to get the position of third special character:
- In case you want to get the position of the third special character in cell B4, then you can do the same steps by replacing the number 2 to 3 in the given formula.
Wrap-Up:
From this tutorial, we have described the simple steps to find the position of the second or third specified character within the text in Excel. Please share your query below in the comment box. We will assist you. To learn more, check out Geek Excel.
Top comments (0)