|
Extract first numeric characters only??...
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? |
Extract first numeric characters only??...
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 "KLZA" wrote in message ... 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? |
Extract first numeric characters only??...
On Mar 5, 10:24*am, "T. Valko" wrote:
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 "KLZA" wrote in message ... 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?- Hide quoted text - - Show quoted text - Hi. Could you type the formula? Google groups is weird when pasting fomulas. It doesn't wok... |
Extract first numeric characters only??...
On Mar 5, 10:32*am, KLZA wrote:
On Mar 5, 10:24*am, "T. Valko" wrote: 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 "KLZA" wrote in message .... 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?- Hide quoted text - - Show quoted text - Hi. *Could you type the formula? *Google groups is weird when pasting fomulas. *It doesn't wok...- Hide quoted text - - Show quoted text - I need th firs formula typed as it shows 1E300 after lookup? |
Extract first numeric characters only??...
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 "KLZA" wrote in message ... On Mar 5, 10:24 am, "T. Valko" wrote: 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 "KLZA" wrote in message ... 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?- Hide quoted text - - Show quoted text - Hi. Could you type the formula? Google groups is weird when pasting fomulas. It doesn't wok... |
Extract first numeric characters only??...
On Mar 5, 10:24*am, "T. Valko" wrote:
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 "KLZA" wrote in message ... 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?- Hide quoted text - - Show quoted text - Hi. Works great! Thanks!! |
Extract first numeric characters only??...
Good deal! Thanks for the feedback!
-- Biff Microsoft Excel MVP "KLZA" wrote in message ... On Mar 5, 10:24 am, "T. Valko" wrote: 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 "KLZA" wrote in message ... 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?- Hide quoted text - - Show quoted text - Hi. Works great! Thanks!! |
Extract first numeric characters only??...
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 "KLZA" wrote in message ... On Mar 5, 10:32 am, KLZA wrote: On Mar 5, 10:24 am, "T. Valko" wrote: 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 "KLZA" wrote in message ... 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?- Hide quoted text - - Show quoted text - Hi. Could you type the formula? Google groups is weird when pasting fomulas. It doesn't wok...- Hide quoted text - - Show quoted text - I need th firs formula typed as it shows 1E300 after lookup? |
| All times are GMT +1. The time now is 08:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com