The formula that Ron posted does exactly what you are asking for... just put
it into A2 (it assumes the filename is in A1, but you can change that
reference to any other cell that you want).

Rick


"Eric" wrote in message
...
Thank you for your suggestions

I would like to remove all characters of the filename, only keep the
number,
and return this number into cell A2.
Let assume that only 1 number existed in each filename, which could be any
number from 0 to 1000.
Does anyone have any suggestions?
Thank anyone for any suggestions
Eric

"Ron Rosenfeld" wrote:

On Fri, 14 Dec 2007 16:34:01 -0800, Eric
wrote:

Does anyone have any suggestions on how to retrieve number from
filename?
Filename: "Eric - up 2 R.xls"
I would like to retrieve the any number within the filename, and return
this
number into cell A2.
Does anyone have any suggestions?
Thanks in advance for any suggetions
Eric

If the numbers are consecutive within the text string, then:

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


If they might not be consecutive, I would use a VBA UDF.
--ron