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

Generic Formula:

If you want to find the N^th occurrence of the certain character, you can use the below formula.

*=FIND(CHAR(160),SUBSTITUTE(text,”@”,CHAR(160),N)) *

Syntax Explanations:

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 N^th 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.

Practical Example:

Refer to the below example image.

Here, we will enter the input text strings in Column B , and the N^th value is given in Column C.

Now we are going to find out the N^th 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.

Closure:

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 *!! **

Related Articles:

Excel Formulas to Extract Nth Word From a Text String ~ Simple Tips!!

Excel Formulas to Count specific word in a range ~ Easy Tutorial!!

Formulas for Extracting Substring Between Two Texts in Excel 365!!

Excel Formulas to Extract the Month Number from Name!!