| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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... |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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... |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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!! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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!! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extract numeric characters plus one character... | Excel Worksheet Functions | |||
| Extract string from apha numeric fields | Excel Discussion (Misc queries) | |||
| only extract numeric value from alpha numeric cell | Excel Discussion (Misc queries) | |||
| Extract Numbers from Alpha-Numeric String | Excel Worksheet Functions | |||
| HOW DO I EXTRACT ALL CHARACTERS AFTER 5 CHARACTERS ON LEFT | Excel Worksheet Functions | |||
Linear Mode
