How to retrieve number from filename?

[Eric]

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

 

[Ron Rosenfeld]

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

 

[Eric]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Ron Rosenfeld]

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

Instead of just repeating your question, it would be more useful if you would
post what happened after you tried my suggestion, and why the result was
unsatisfactory.

There is no way for me to figure out the problem with my suggestion when you
don't post any additional detail.


--ron