Extract numbers only

[sujittalukde]

Also posted at mrexcel forum but not getting suitalble reply.
http://www.mrexcel.com/board2/viewtopic.php?t=280330
How can I extract the numbers only from the followings:

Up 89.70
Up 168.56
Down 256.74

etc.

please note that the numbers may be of any digit

Also,Text to column method I cant use as I am copying data from web page via web qwery and in another sheet I set formula for calculation. earlier no such "up" / "down" was there but now the format has been changed and thus I need a formula to get the result

[Charlize]

Sub remove_up_down()
Dim cell As Range
For Each cell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
cell.Value = Split(cell.Value, " ")(1)
Next cell
End Subor if you want a formula, try this one :
=VALUE(MID(A1;SEARCH(" ";A1;1)+1;LEN(A1)))maybe you've got to replace the ; with , in this formula.

[unmarkedhelicopter]

Charlize, this only works if there is one word and it's at the front and the number follows the space [ =VALUE(MID(A1,SEARCH(" ",A1,1)+1,LEN(A1))) ]
I found a formula from NVBC :-
=--REPLACE(LEFT(A1,LOOKUP(10,--MID(A1,ROW(INDIRECT("1:30")),1),ROW(INDIRECT("1:30")))),1,MIN(FIND(0,SUBSTITUTE(A1&0,{1,2,3,4,5,6,7,8,9},0)))-1,"") This will extract all numbers from a string (though Bob doesn't like this ! :) )

[Bob Phillips]

Still don't like using iNDIRECT unnecessarily

=--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),
SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))

[RichardSchollar]

And another:

=-LOOKUP(0,-MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),255))

Richard

[sujittalukde]

Thanks to all for the support

[Bob Phillips]

That one is not as generic though Richard, as it fails on say 'a 123 b'

[RichardSchollar]

That one is not as generic though Richard, as it fails on say 'a 123 b'

Hi Bob

No that's true - it only works if the number is at the end.

This is generic, but then involves the use of Indirect:

=-LOOKUP(0,-MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

My Dad broke out his 18yr old Glenfiddich last night. Very smooth :thumb