Find the nth occurrence of a string or character in Excel

This Tip for Excel shows you how to find the nth (3rd, 4th, 5th) occurrence of a character in a cell.

Let’s assume you have the text Tips-For-Excel in cell A1.  To find the first dash (the one in green) you could easily do =FIND(“-”,A1) and this would let you know that the first dash occurs as the 5th character.  But what if we want to know the location of the second dash?  The find function has three arguments:

find_text: this is what we’re searching for.  In this case, it’s “-”.

within_text:  what cell or string we’re searching in.  This case it’s A1.

[start_num]:  the square brackets indicate this argument is optional.    Here we can say where in cell A1 to start looking from.

in our example, the find() function finds the first occurrence of the “-”.  So if we want to find the 2nd occurrence, we simply start after the first occurrence.

so use =FIND(“-”,A1,FIND(“-”,A1)+1)

 

the +1 means you start after the first occurrence.  Continue nesting the find() formula to find the 3rd, 4th etc…

 

Leave a Reply

  

  

  


1 × seven =

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>