26
Excel Formulas to Find the Nth Occurrence of Character ~ Simple Tricks!!
Assume that you have a list of text strings mixed with special characters. Here, you have to find out the position of the N th occurrence of a certain character. How could you get them quickly? This article provides some useful tricks to get it done. Let’s jump into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

- If you want to find the Nth occurrence of the certain character, you can use the below formula.
*=FIND(CHAR(160),SUBSTITUTE(text,”@”,CHAR(160),N)) *
- FIND – In Excel, the*FIND function* helps to find out the one text string inside the other.
- CHAR – This function is used to get a character by a specified number. Read more on the CHAR function.
- SUBSTITUTE – This function helps to replace existing text with new text in a text string when you want to replace text based on its content, not position. Read more on the SUBSTITUTE function.
- Text – It represents the input value.
- N – It specifies the Nth occurrence 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.
Refer to the below example image.
- Here, we will enter the input text strings in Column B , and the Nth value is given in Column C.
- Now we are going to find out the Nth occurrence of the “@” symbol.

- Select any cell and type the above-given formula.

- Finally, press the ** ENTER** key, you can get the result as shown below.

Here we have shown the simple formulas to find the Nth occurrence of the special character in Excel. Hope you like it. If you have any questions , feel free to comment.
Thank you so much for visiting *Geek Excel!! **If you want to learn more helpful formulas, check out Excel Formulas *!! **
26