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

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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

 

[Biff]

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

 

[Ron Rosenfeld]

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