Return position of 2nd, 3rd, ect occurrence of a character in a st
 

the Excel =FIND command returns the position of the first instance of a
string that it finds. Is there any way to find position of the 2nd, 3rd, or
nth occurrence? Using =MID is too messy.

Answer: Return position of 2nd, 3rd, ect occurrence of a character in a st
 

Yes, there is a way to find the position of the 2nd, 3rd, or nth occurrence of a character in a string in Excel.

You can use a combination of the FIND and SUBSTITUTE functions to achieve this.

Here's an example of how to find the position of the 2nd occurrence of the letter "e" in the string "Excel is awesome":

1. Start by using the FIND function to find the position of the first occurrence of "e" in the string. The formula would be:
   Formula:

   ---

   =FIND("e", "Excel is awesome") 


   ---

2. Next, use the SUBSTITUTE function to replace the first occurrence of "e" with a different character that is not in the string. For example, you could use the "|" character. The formula would be:
   Formula:

   ---

   =SUBSTITUTE("Excel is awesome", "e", "|", 1) 


   ---

3. Now that the first occurrence of "e" has been replaced, you can use the FIND function again to find the position of the second occurrence of "e". The formula would be:
   Formula:

   ---

   =FIND("e", SUBSTITUTE("Excel is awesome", "e", "|", 1)) 


   ---

4. Finally, subtract the length of the string up to the first occurrence of "e" (which you found in step 1) from the position of the second occurrence of "e" (which you found in step 3) to get the position of the second occurrence of "e". The formula would be:
   Formula:

   ---

   =FIND("e", SUBSTITUTE("Excel is awesome", "e", "|", 1)) - FIND("e", "Excel is awesome") 


   ---

This will return the position of the second occurrence of "e" in the string "Excel is awesome". You can modify the formula to find the position of the 3rd, 4th, or nth occurrence by adjusting the number in the SUBSTITUTE function to replace the appropriate occurrence of the character.

Return position of 2nd, 3rd, ect occurrence of a character in a st
 

You could nest the find commands, using the result of the first, plus 1, as
the starting point for the next:
=FIND("a",A1,FIND("a",A1)+1) finds the second "a" within cell A2, for
instance.
=FIND("a",A1,FIND("a",A1,FIND("a",A1)+1)+1) finds the third.
Of course, this is subject to Excel's limit of seven levels of nesting.

"jheby" wrote:

the Excel =FIND command returns the position of the first instance of a
string that it finds. Is there any way to find position of the 2nd, 3rd, or
nth occurrence? Using =MID is too messy.

Return position of 2nd, 3rd, ect occurrence of a character in a st
 

Try something like this:

For a text value in A1

B1: =FIND(CHAR(7),SUBSTITUTE(A1,"a",CHAR(7),3))

That formula finds the position of the 3rd instance of the letter "a" in
Cell A1

Note: Char(7) is ASCII for the Bell sound...unlikely that it's in your
string, right?

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"jheby" wrote:

the Excel =FIND command returns the position of the first instance of a
string that it finds. Is there any way to find position of the 2nd, 3rd, or
nth occurrence? Using =MID is too messy.

Return position of 2nd, 3rd, ect occurrence of a character in a st
 

Hi!

Try this:

=FIND("~",SUBSTITUTE(A1,"x","~",B1))

Searching for "x". B1 holds the instance you want.

Note: Substitute is case sensitive. To make it a little more robust:

=FIND("~",SUBSTITUTE(UPPER(A1),"X","~",B1))

"~" is used as a "marker". The marker needs to be some char or group of
chars that are more than likely not to appear in the string.

Biff

"jheby" wrote in message
...
the Excel =FIND command returns the position of the first instance of a
string that it finds. Is there any way to find position of the 2nd, 3rd,
or
nth occurrence? Using =MID is too messy.

Return position of 2nd, 3rd, ect occurrence of a character in a st
 

On Tue, 14 Feb 2006 11:12:26 -0800, "jheby"
wrote:

the Excel =FIND command returns the position of the first instance of a
string that it finds. Is there any way to find position of the 2nd, 3rd, or
nth occurrence? Using =MID is too messy.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr


Then use the formula:

=REGEX.FIND(StringToSearch, StringToFind, n)

where 'n' is the instance number of the string.

For a case-INsensitive version:

=REGEX.FIND(StringToSearch, StringToFind, n, FALSE)


--ron