Parsing 124A into 124

C

Christophe

Hello:
I am looking for a way to parse the examples below in excel:
124 A into 124
12345 into 12345
55/49 into 55
888b into 888

Does such function exist in excel or any idea how I could implement it
in VBA? The longest serie of digits before a non numeric caracters is
what I am looking for.

Thanks a lot,
Chris
 
D

Dave Peterson

How about:

=--LEFT(A1,MATCH(FALSE,
ISNUMBER(-MID(A1&"x",ROW(INDIRECT("1:"&LEN(A1)+1)),1)),0)-1)
(all one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

It'll provide an error if there are no leading digits (like: ABC1234).
 
R

Ron Rosenfeld

Hello:
I am looking for a way to parse the examples below in excel:
124 A into 124
12345 into 12345
55/49 into 55
888b into 888

Does such function exist in excel or any idea how I could implement it
in VBA? The longest serie of digits before a non numeric caracters is
what I am looking for.

Thanks a lot,
Chris
Click to expand...

Array formula:

=IF(ISNUMBER(A1),A1,LEFT(A1,MATCH(FALSE,ISNUMBER(
-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)-1))

To enter an array formula, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula.

The above returns the number as a text string. If you require it to be an
actual number, prefix the formula with a double unary:

=--IF(ISNUMBER(A1),A1,LEFT(A1,MATCH(FALSE,ISNUMBER(
-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)-1))

Note that the formulas assume your "number" ends with the first NON-numeric
character.


--ron
 
Top