Finding the alphabet in the soup

[K]

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

 

[Tom Ogilvy]

=LEFT(A1,MAX(IF(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),ROW(INDIRE
CT("1:"&LEN(A1))))))

Entered with Ctrl+shift+Enter Rather than just enter since this is an array
formula.

 

[Trevor Shuttleworth]

Hi Tom

is there an easy way of explaining just what this formula does ?

Regards

Trevor

 

[Frank Kabel]

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