Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
To extract the number if there is niumeric in right most digits
Please execuse me for the posting of this question again.
I am using the following formula for extracting numeric numbers. But I want to extract the numeric numbers only if the first digit is numeric in a cell otherwise the result cell should be null. for example if the cell data is '123AAA' it should be extracted as 123 if the cell is AAA123 the result should be Null or empty spaces. not should be shown as 'N/A' existing formula myFormula = "=LOOKUP(6.022*10^23,--MID(RC[1]," _ & "MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RC[1]" _ & "&""0123456789"")),ROW(INDIRECT(""1:""&LEN(RC[1])))))" .Range(.Cells(2, res), .Cells(LastRow, res)).FormulaR1C1 = myFormula Please help with thanks Polachan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
To extract the number if there is niumeric in right most digits
Add an if statement and use IsNumber to get jsut the 1st character of the cell
"=if(isNumber(Left(RC[1],1)),LOOKUP(6.022*10^23,--MID(RC[1]," _ & "MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RC[1]" _ & "&""0123456789"")),ROW(INDIRECT(""1:""&LEN(RC[1]))))),"")" "pol" wrote: Please execuse me for the posting of this question again. I am using the following formula for extracting numeric numbers. But I want to extract the numeric numbers only if the first digit is numeric in a cell otherwise the result cell should be null. for example if the cell data is '123AAA' it should be extracted as 123 if the cell is AAA123 the result should be Null or empty spaces. not should be shown as 'N/A' existing formula myFormula = "=LOOKUP(6.022*10^23,--MID(RC[1]," _ & "MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RC[1]" _ & "&""0123456789"")),ROW(INDIRECT(""1:""&LEN(RC[1])))))" .Range(.Cells(2, res), .Cells(LastRow, res)).FormulaR1C1 = myFormula Please help with thanks Polachan |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
To extract the number if there is niumeric in right most digits
Try this formula
=IF(ISNUMBER(VALUE(LEFT(A1,1))),LOOKUP(10^23,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000)))),"") Mike "pol" wrote: Please execuse me for the posting of this question again. I am using the following formula for extracting numeric numbers. But I want to extract the numeric numbers only if the first digit is numeric in a cell otherwise the result cell should be null. for example if the cell data is '123AAA' it should be extracted as 123 if the cell is AAA123 the result should be Null or empty spaces. not should be shown as 'N/A' existing formula myFormula = "=LOOKUP(6.022*10^23,--MID(RC[1]," _ & "MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RC[1]" _ & "&""0123456789"")),ROW(INDIRECT(""1:""&LEN(RC[1])))))" .Range(.Cells(2, res), .Cells(LastRow, res)).FormulaR1C1 = myFormula Please help with thanks Polachan |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
To extract the number if there is niumeric in right most digits
Check one of your other posts.
pol wrote: Please execuse me for the posting of this question again. I am using the following formula for extracting numeric numbers. But I want to extract the numeric numbers only if the first digit is numeric in a cell otherwise the result cell should be null. for example if the cell data is '123AAA' it should be extracted as 123 if the cell is AAA123 the result should be Null or empty spaces. not should be shown as 'N/A' existing formula myFormula = "=LOOKUP(6.022*10^23,--MID(RC[1]," _ & "MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RC[1]" _ & "&""0123456789"")),ROW(INDIRECT(""1:""&LEN(RC[1])))))" .Range(.Cells(2, res), .Cells(LastRow, res)).FormulaR1C1 = myFormula Please help with thanks Polachan -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
To extract the number if there is niumeric in right most digit
Thanks a lot thanks very much
"Dave Peterson" wrote: Check one of your other posts. pol wrote: Please execuse me for the posting of this question again. I am using the following formula for extracting numeric numbers. But I want to extract the numeric numbers only if the first digit is numeric in a cell otherwise the result cell should be null. for example if the cell data is '123AAA' it should be extracted as 123 if the cell is AAA123 the result should be Null or empty spaces. not should be shown as 'N/A' existing formula myFormula = "=LOOKUP(6.022*10^23,--MID(RC[1]," _ & "MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RC[1]" _ & "&""0123456789"")),ROW(INDIRECT(""1:""&LEN(RC[1])))))" .Range(.Cells(2, res), .Cells(LastRow, res)).FormulaR1C1 = myFormula Please help with thanks Polachan -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to extract digits from a row of numbers | Excel Worksheet Functions | |||
How to customize number to 10 digits including 2 digits after deci | Excel Worksheet Functions | |||
I need to extract the first 3 digits from a cell (ex: AAG12345)? | Excel Worksheet Functions | |||
how do I extract hex digits in a cell and convert to binary | Excel Worksheet Functions | |||
Formula to extract digits from a text string? | Excel Worksheet Functions |