Find Function

[SJT]

I would like to find the second occurence of a specific alphanumeric
character in a cell. For example, if cell A1 contains "BOBBY" what would be
the formula for finding the second occurence of the letter "B". Thank you
advance for your assistance. Greatly appreciated

 

[Niek Otten]

=FIND("#",SUBSTITUTE(A1,"B","#",2))

Replace # by another symbol if it can be part of your text

 

[מיכ×ל (מיקי) ×בידן]

In cell B1 try: =FIND("B",A9,2)
Micky

 

[Luke M]

=FIND("B",SUBSTITUTE(A1,"B",CHAR(160),1))

Note that CHAR(160) is simply a non-printable character rarely used in
normal usage. The formula replaces the first instance of desired letter, and
then finds the 2nd instance. Alternatively:

=FIND(CHAR(160),SUBSTITUTE(A1,"B",CHAR(160),2))

Could be used, if you want to control which instance of the letter to find
(by simply changing the last arguement)

 

[מיכ×ל (מיקי) ×בידן]

A9 should read: A1
Micky

In cell B1 try: =FIND("B",A9,2)
Micky

 

[SJT]

Thank you Niek.

=FIND("#",SUBSTITUTE(A1,"B","#",2))

Replace # by another symbol if it can be part of your text

--
Kind regards,

Niek Otten
Microsoft MVP - Excel