Parsing 124A into 124
 

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 character is
what I am looking for.

Thanks a lot,
Chris

one way, assuming the data is in cell B3

=IF(NOT(ISERROR(VALUE(B3))),B3,LEFT(B3,MATCH(0,(CO DE(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))47)*(COD E(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))<58)*ROW(I NDIRECT("1:"&LEN(B3))),0)-1))

when you enter the formula, you must hit Cntrl+Shift+Enter.



"Christophe" wrote:

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 character is
what I am looking for.

Thanks a lot,
Chris

a little bit shorter

=IF(NOT(ISERROR(VALUE(B3))),B3,LEFT(B3,MATCH(0,(CO DE(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))47)*(COD E(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))<58),0)-1))

"Christophe" wrote:

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 character is
what I am looking for.

Thanks a lot,
Chris

Another possible array formula (entered with CTRL-SHIFT-ENTER), for data in A1:

=MAX(IF(ISNUMBER(--LEFT(A1,ROW($1:$50))),--LEFT(A1,ROW($1:$50))))

If necessary replace both occurrences of 50 with a sensible upper limit for
the string lengths you have.

"Christophe" wrote:

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 character is
what I am looking for.

Thanks a lot,
Chris

"Ron Moore" wrote...
Another possible array formula (entered with CTRL-SHIFT-ENTER), for
data in A1:

=MAX(IF(ISNUMBER(--LEFT(A1,ROW($1:$50))),--LEFT(A1,ROW($1:$50))))

If necessary replace both occurrences of 50 with a sensible upper
limit for the string lengths you have.
....

There's not much gained using 50 rather than 1000 (or 10). Also, there's a
difference between locating the leftmost longest substring that could be
converted into a number and the leftmost longest substring of decimal
digits. Your formula returns errors when the first character is either a
dash or period followed by decimal digits.

As an alternative,

=--LEFT(A1,LOOKUP(1E300,-LEFT(A1,ROW($1:$50)),ROW($1:$50)))

which doesn't need to be entered as an array formula.