How to retrieve the number?

[Eric]

Does anyone have any suggestions on how to retrieve the number from filename?

Let assume that only 1 number existed within each filename, which could be
any number from 0 to 1000, such as "Eric - UP 4 R.xls". I would like to
remove all characters of the filename except the number, and return this
number into cell A2.

Does anyone have any suggestions?
Thank anyone for any suggestions
Eric

 

[T. Valko]

Let assume that only 1 number existed within each filename, which could be

any number from 0 to 1000

Try this:

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

If any numbers have leading 0s they'll get dropped.

 

[Eric]

Thank you very much for your suggestions
There is no written filename in cell A1, do you have any suggestions on how
to retrieve the filename for this worksheet and combine it with your coding?
Thank you very much for any suggestions
Eric

 

[David Biddulph]

The CELL function will let you retrieve the filename. Details in Excel
help.

 

[Bob Phillips]

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)