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

[Ron Coderre]

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.

[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

"jheby" <jh...@discussions.microsoft.com> wrote in message
news:75326C1E-0E12-480E...@microsoft.com...

[Ron Rosenfeld]

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