Find Function

  • Thread starter Thread starter SJT
  • Start date Start date
S

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
 
=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
 
=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


מיכ×ל (מיקי) ×בידן said:
In cell B1 try: =FIND("B",A9,2)
Micky
Click to expand...
 
Thank you Niek.

Niek Otten said:
=FIND("#",SUBSTITUTE(A1,"B","#",2))

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel
Click to expand...
 
Back
Top