To extract the number if there is niumeric in right most digits

P

pol

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
 
J

Joel

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]))))),"")"
 
M

Mike H

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&"0123456789")),ROW($1:$10000)))),"")


Mike
 
D

Dave Peterson

Check one of your other posts.


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
Click to expand...
 
P

pol

Thanks a lot thanks very much

Dave Peterson said:
Check one of your other posts.


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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

To store the formula in a string 4
macro to copy following cells in the same column 4
Macro - Formula RC[?] with ? being a variable number 5
if there is a blank ,how to get the blank cell in a range be highlighted 2
Macro to search range for a portion of a specific value and if findit - then paste formula in anothe 2
Why are the two codes below not getting the right formula for myworksheet event change formula? 2
to extract numeric figures in left side od the alpha charectors pl 1
how to find text vaues for a given grade? 0

Top