Explanation
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. For a demo of the CHAR function, see this video.
Finally, the FIND character looks for CHAR(160) and returns the position.
Note: I developed this example on a Mac originally, where CHAR(160) resolves to a dagger (†). On Windows, use CHAR(134) for a dagger character.
Related formulas
Extract nth word from text string
Replace one character with another
Extract last two words from cell
Related functions
SUBSTITUTE Function
The Excel SUBSTITUTE function replaces text in a given string by matching. For example =SUBSTITUTE("952-455-7865","-","") returns "9524557865"; the dash is stripped. SUBSTITUTE is case-sensitive and does not support wildcards....
FIND Function
The Excel FIND function returns the position (as a number) of one text string inside another. When the text is not found, FIND returns a #VALUE error.
CHAR Function
The Excel CHAR function returns a character when given a valid character code. CHAR can insert characters that are hard to enter into a formula. For example, CHAR(10) returns a line break and can be used to add a line break to text in a formula.
