Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
pol pol is offline
external usenet poster
 
Posts: 129
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.misc
pol pol is offline
external usenet poster
 
Posts: 129
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to extract digits from a row of numbers preydd Excel Worksheet Functions 2 September 3rd 08 04:32 PM
How to customize number to 10 digits including 2 digits after deci Carina Excel Worksheet Functions 3 September 20th 07 02:50 AM
I need to extract the first 3 digits from a cell (ex: AAG12345)? Rob Excel Worksheet Functions 6 April 21st 07 11:45 PM
how do I extract hex digits in a cell and convert to binary prs_013 Excel Worksheet Functions 3 March 17th 06 08:14 AM
Formula to extract digits from a text string? [email protected] Excel Worksheet Functions 7 January 15th 06 05:16 AM


All times are GMT +1. The time now is 12:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"