Extract first numeric characters only??...

K

KLZA

Hi. I need to extract the first few numbers from a cell an ignore the
rest. So If cell A1 shows 12468ABC13 the result should be 12468 or if
cell A1 shows 123ABC45684 the result should be 123. Is this possible?
 
T

T. Valko

One way:

=LOOKUP(1E300,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))

Note that this will strip off any leading zeros.

0123ABC will return 123.

If you have leading zeros you can use this version *but* it returns the
value as TEXT, not a number:

=LEFT(A1,SUMPRODUCT(--ISNUMBER(-MID(A1,ROW(INDIRECT("1:50")),1))))
 
K

KLZA

One way:

=LOOKUP(1E300,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))

Note that this will strip off any leading zeros.

0123ABC will return 123.

If you have leading zeros you can use this version *but* it returns the
value as TEXT, not a number:

=LEFT(A1,SUMPRODUCT(--ISNUMBER(-MID(A1,ROW(INDIRECT("1:50")),1))))

--
Biff
Microsoft Excel MVP






- Show quoted text -
Click to expand...

Hi. Could you type the formula? Google groups is weird when pasting
fomulas. It doesn't wok...
 
K

KLZA

Hi.  Could you type the formula?  Google groups is weird when pasting
fomulas.  It doesn't wok...- Hide quoted text -

- Show quoted text -
Click to expand...

I need th firs formula typed as it shows 1E300 after lookup?
 
T

T. Valko

Google groups is weird when pasting fomulas

Yeah, I know! It likes to insert dashes (-).

I'll write the formulas in chunks. When you apply them to your worksheet the
formulas are all on one line.

This one strips leading zeros:

=LOOKUP(1E300,
--LEFT(A1,ROW(
INDIRECT("1:"&LEN(A1)))))

This one retains leading zeros but returns TEXT:

=LEFT(A1,SUMPRODUCT(
--ISNUMBER(-MID(A1,
ROW(INDIRECT("1:50")),1))))


--
Biff
Microsoft Excel MVP


One way:

=LOOKUP(1E300,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))

Note that this will strip off any leading zeros.

0123ABC will return 123.

If you have leading zeros you can use this version *but* it returns the
value as TEXT, not a number:

=LEFT(A1,SUMPRODUCT(--ISNUMBER(-MID(A1,ROW(INDIRECT("1:50")),1))))

--
Biff
Microsoft Excel MVP






- Show quoted text -
Click to expand...

Hi. Could you type the formula? Google groups is weird when pasting
fomulas. It doesn't wok...
 
K

KLZA

One way:

=LOOKUP(1E300,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))

Note that this will strip off any leading zeros.

0123ABC will return 123.

If you have leading zeros you can use this version *but* it returns the
value as TEXT, not a number:

=LEFT(A1,SUMPRODUCT(--ISNUMBER(-MID(A1,ROW(INDIRECT("1:50")),1))))

--
Biff
Microsoft Excel MVP






- Show quoted text -
Click to expand...

Hi. Works great! Thanks!!
 
T

T. Valko

Good deal! Thanks for the feedback!

--
Biff
Microsoft Excel MVP


One way:

=LOOKUP(1E300,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))

Note that this will strip off any leading zeros.

0123ABC will return 123.

If you have leading zeros you can use this version *but* it returns the
value as TEXT, not a number:

=LEFT(A1,SUMPRODUCT(--ISNUMBER(-MID(A1,ROW(INDIRECT("1:50")),1))))

--
Biff
Microsoft Excel MVP






- Show quoted text -
Click to expand...

Hi. Works great! Thanks!!
 
T

T. Valko

Yes, that's intentional.

1E300 is very big number = 1 followed by 300 zeros.

1E300 is scientific notation and a shortcut for expressing 1 followed by 300
zeros.

--
Biff
Microsoft Excel MVP


Hi. Could you type the formula? Google groups is weird when pasting
fomulas. It doesn't wok...- Hide quoted text -

- Show quoted text -
Click to expand...

I need th firs formula typed as it shows 1E300 after lookup?
 
Top