Quick, clean, and to the point

Find nth occurrence of character

Excel formula: Find nth occurrence of character
Generic formula 

To find the nth occurrence 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:


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):


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.

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Charts video training course
Video training for Excel Tables
Dynamic Array Formulas