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

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

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

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

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