Formula for Extracting Alphabetic Part of a Product Code

O

ob3ron02

Hi,

I've got a column filled with part #s having a format like ABC143
where the number of letters varies between 2-4 and the number of digit
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 alphabeti
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. There
gotta be an easier way than that! Note that IsText() reports ACB12 a
true, so I'd have to test IsText on the right side until I get fals
and then find the length of the string and subtract from that and tak
left() of the result... what a pain!

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

Thanks for any tips or hints!

To
 
F

Frank Kabel

Hi
try the follo´wing array formula (entered with CTRL+SHIFT+ENTER):
=LEFT(A1,MIN(IF(ISNUMBER(-MID(A1,seq,1)),seq)-1)

where seq is the defined name with the formula:
=ROW(INDIRECT("1:1024"))
 
I

ivano

Frank Kabel said:
Hi
try the follo´wing array formula (entered with CTRL+SHIFT+ENTER):
=LEFT(A1,MIN(IF(ISNUMBER(-MID(A1,seq,1)),seq)-1)

where seq is the defined name with the formula:
=ROW(INDIRECT("1:1024"))
Click to expand...

hy Frank,
I think you have lost a "-" in your formula:
=LEFT(A1,MIN(IF(ISNUMBER(--MID(A1,seq,1)),seq)-1)

ivano
 
F

Frank Kabel

ivano said:
hy Frank,
I think you have lost a "-" in your formula:
=LEFT(A1,MIN(IF(ISNUMBER(--MID(A1,seq,1)),seq)-1)
Click to expand...

Hi
no this was intentioanlly :)
No need for a second '-' sign as I just test if the returned character
is a number and I don't care about the sign of this number for this
comparison.
Frsnk
 
A

Aladin Akyurek

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 wit
enter.

A1 houses a value like ABC1439
 
I

ivano

Frank Kabel said:
Hi
no this was intentioanlly :)
No need for a second '-' sign as I just test if the returned character
is a number and I don't care about the sign of this number for this
comparison.
Frsnk
Click to expand...

You are right, Frank...
I thought that yuor formula don't play for "-", insteat missing a ")"

=LEFT(A1,MIN(IF(ISNUMBER(-MID(A1,seq,1)),seq))-1)

hy..ivano
 
Top