Re: Finding the alphabet in the soup

From: Frank Kabel (frank.kabel_at_freenet.de)
Date: 03/30/04 

Date: Wed, 31 Mar 2004 00:37:25 +0200

Hi
though not Tom It's not that difficult :-)
1. The part
MID(A1,ROW(INDIRECT("1:"&LEN(A1)))
iterates through each charatcer in the string A1 using the ROW number
as starting point for the MID function. That is for example
ROW(INDIRECT("1:10"))
returns the values 1 to 10 if this formula is entered as array formula

2. ISNUMBER tests if the substring is a number. If yes the cell
position is returned (again using the ROW/INDIRECT combination)

3. The MAX function returns the largest position of a number

4. The rest is just a simple LEFT function using the last numeric value
as ending point 

--
Regards
Frank Kabel
Frankfurt, Germany
"Trevor Shuttleworth" <Trevor@Shucks.demon.co.uk> schrieb im
Newsbeitrag news:umQiJJqFEHA.2408@TK2MSFTNGP10.phx.gbl...
> Hi Tom
>
> is there an easy way of explaining just what this formula does ?
>
> Regards
>
> Trevor
>
>
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:eTqokhpFEHA.3640@tk2msftngp13.phx.gbl...
> >
>
=LEFT(A1,MAX(IF(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),ROW(I
NDIRE
> > CT("1:"&LEN(A1))))))
> >
> > Entered with Ctrl+shift+Enter Rather than just enter since this is
an
> array
> > formula.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "K" <mompara@sympatico.ca> wrote in message
> > news:iKjac.47316$1A6.887988@news20.bellglobal.com...
> > > Hello
> > > I have variable string from which I want to extract from the left
all
> > > those characters up to before the second character that is an
alphabet
> > > (rather than a number).
> > > In the case where Sstring = C89Butter the extraction would return
"C89".
> > >
> > > If I know that the character I am using is "B" then I can use:
> > > mid(Sstring,1,Find("B",Sstring,2)) to yield "C89"
> > >
> > > However, in my case, all I know is that the character is any
alphabet.
> > > Appreciate any ideas.
> > >
> > > Thanks
> > > K
> > >
> >
> >
>
>
Quantcast