Quick, clean, and to the point

Position of 2nd 3rd etc instance of character

Excel formula: Position of 2nd 3rd etc instance of character
Generic formula 

To get the position of the 2nd, 3rd, 4th, etc. instance of a specific character inside a text string, you can use the FIND and SUBSTITUTE functions.

In the example shown, the formula in E4 is:


At the core, this formula uses the fact that the SUBSTITUTE function understands "instance", supplied as an optional fourth argument called "instance_num". This means you can use the SUBSTITUTE function to replace a specific instance of a character in a text string. So:


replaces only the 2nd instance (2 comes from D4) of "x" in text in B4, with "~" character. The result looks like this:


Next, FIND locates the "~" inside this string and returns the position, which is 7 in this case.

Note: we use "~" in this case only because it rarely occurs in other text. You can use any character that you know won't appear in the text.

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 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac 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