How to retrieve number from filename?
 

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

How to retrieve number from filename?
 

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

How to retrieve number from filename?
 

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

How to retrieve number from filename?
 

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

How to retrieve number from filename?
 

On Fri, 14 Dec 2007 21:16:00 -0800, Eric
wrote:

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