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.

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.

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.

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" <jheby@discussions.microsoft.com> wrote in message
news:75326C1E-0E12-480E-B3BD-379329CF7661@microsoft.com...
> 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.

On Tue, 14 Feb 2006 11:12:26 -0800, "jheby" <jheby@discussions.microsoft.com>
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