Removing numerical text values and leaving only alpha letters

[Executioner]

Hi everyone,
I have one column A of data that looks like this:

AAL2074883-XX / A
AAL2075713-XX / A
AAL2075715-XX / A
AP2925411 / D
AP2928142 / H
AP2928142 / J

I have inserted a new column B so I can filter the alpha characters only. In the example above, it should look like:

A                        B
AAL2074883-XX /  A	AAL
AAL2075713-XX /  A	AAL
AAL2075715-XX /  A	AAL
AP2925411 /  D	        AP
AP2928142 /  H	        AP
AP2928142 /  J	        AP

The formula that I'm working with looks like this:
=LEFT(A2,3-ISNUMBER(MID(A2,2,1)+0))

This works only for the first 3 rows. Rows with only 2 alpha characters show up with a number - AP2 for example. Not sure how to adjust my formula to make it work correctly. Thanks for your help.

 

[Andrew Poulsom]

Try eg:

=LEFT(A2,MATCH(TRUE,INDEX(ISNUMBER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0),),FALSE)-1)

 

[pgc01]

Hi

Another option:

=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)

 

[meldoc]

Hello

Or this, another one of Andrew's modified slightly.

=SUBSTITUTE(A2,MID(A2,MIN(SEARCH({0;1;2;3;4;5;6;7;8;9},A2&1234567890)),100),"")