laika222
9/19/2017 - 3:40 PM

FIND, SUBSTITUTE

Find Nth occurrence of a character in a string.

How to find Nth occurence of a character in Excel using FIND and SUBSTITUTE functions. SUBSTITUTE format is SUBSTITUTE ("full text string", "character you're looking for", "character you want to substitute", number of occurence that you're looking for). In the example below, you're not trying to substitute the character out, so apparently putting in CHAR(160) doesn't substitute it. Below, the J17 is the cell that contains the full text string, "," is the character being looked for, CHAR(160) tells it to not substitute the character out, and 3 tells it to find the third instance of the character. Therefore, it'll return the position of the third comma in the string found in cell J17.

=FIND(CHAR(160),SUBSTITUTE(J17,",",CHAR(160),3))