Find Function
in [General]
|
Prev: How do I look at two worksheets in one Excel workbook at same time
Next: Conditonal Format based on two different criteria
From: SJT on 4 Jun 2010 14:58 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
From: Niek Otten on 4 Jun 2010 15:11 =FIND("#",SUBSTITUTE(A1,"B","#",2)) Replace # by another symbol if it can be part of your text -- Kind regards, Niek Otten Microsoft MVP - Excel "SJT" <SJT(a)discussions.microsoft.com> wrote in message news:70B9153E-4351-4983-96D5-8870C6330D3D(a)microsoft.com... >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 >
From: מיכאל (מיקי) אבידן on 4 Jun 2010 15:13 In cell B1 try: =FIND("B",A9,2) Micky "SJT" wrote: > 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 >
From: Luke M on 4 Jun 2010 15:14 =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) -- Best Regards, Luke M "SJT" <SJT(a)discussions.microsoft.com> wrote in message news:70B9153E-4351-4983-96D5-8870C6330D3D(a)microsoft.com... >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 >
From: מיכאל (מיקי) אבידן on 4 Jun 2010 16:03
A9 should read: A1 Micky "מיכאל (מיקי) אבידן" wrote: > In cell B1 try: =FIND("B",A9,2) > Micky > > > "SJT" wrote: > > > 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 > > |