Find nth occurrence of character

Find nth occurrence of character

Generic formula 

=FIND(CHAR(160),SUBSTITUTE(text,"@",CHAR(160),N))

Related formulas 

Extract nth word from text string

Replace one character with another

Extract last two words from cell

Explanation

To find the nth occurence of a character in a text string, you can use a formula based on the FIND and SUBSTITUTE functions.

In the example shown, the formula in D5 is:

=FIND(CHAR(160),SUBSTITUTE(B5,"@",CHAR(160),C5))

How this formula works

In this example we are looking for the nth occurrence of the "@" character.

Working from the inside out, we first use the SUBSTITUTE function to replace the nth occurrence of "@" with CHAR(160):

SUBSTITUTE(B5,"@",CHAR(160),C5)

The SUBSTITUTE function has an optional 4th argument called instance number that can be used to specify the instance that should be replaced. This number comes from column C. 

SUBSTITUTE then replaces the nth occurrence of "@" with CHAR(160), which resolves to "†". We use CHAR(160) because it won't normally appear in text. You can use any character you know won't exist in the text.

Finally, the FIND character looks for CHAR(160) and returns the position.

Find nth occurrence of character

Generic formula 

=FIND(CHAR(160),SUBSTITUTE(text,"@",CHAR(160),N))

Related formulas 

Extract nth word from text string

Replace one character with another

Extract last two words from cell

Explanation

To find the nth occurence of a character in a text string, you can use a formula based on the FIND and SUBSTITUTE functions.

In the example shown, the formula in D5 is:

=FIND(CHAR(160),SUBSTITUTE(B5,"@",CHAR(160),C5))

How this formula works

In this example we are looking for the nth occurrence of the "@" character.

Working from the inside out, we first use the SUBSTITUTE function to replace the nth occurrence of "@" with CHAR(160):

SUBSTITUTE(B5,"@",CHAR(160),C5)

The SUBSTITUTE function has an optional 4th argument called instance number that can be used to specify the instance that should be replaced. This number comes from column C. 

SUBSTITUTE then replaces the nth occurrence of "@" with CHAR(160), which resolves to "†". We use CHAR(160) because it won't normally appear in text. You can use any character you know won't exist in the text.

Finally, the FIND character looks for CHAR(160) and returns the position.