B1:

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

which must be confirmed with control+shift+enter instead of just with
enter.

A1 houses a value like ABC1439

ob3ron02 Wrote:
Hi,

I've got a column filled with part #s having a format like ABC1439
where the number of letters varies between 2-4 and the number of digits
varies between 3-4. Ie these are all possible numbers: AB145, BDA1457,
KDOG145, etc. What I'd like is to be able to extract the alphabetic
part of the part #s.

I know I could probably work something out with nested If statements,
IsText(), Right() and Left() but that would be really messy. Theres
gotta be an easier way than that! Note that IsText() reports ACB12 as
true, so I'd have to test IsText on the right side until I get false
and then find the length of the string and subtract from that and take
left() of the result... what a pain!

I know I can do it in VB as well, but since I've already got a formula
in place (it only takes the leftmost two letters) it would be easiest
just to change the formula.

Thanks for any tips or hints!

Tom


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=273584