How to retrieve the number?
 

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

How to retrieve the number?
 

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.

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
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

How to retrieve the number?
 

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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
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

How to retrieve the number?
 

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

"Eric" wrote in message
...
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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
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

How to retrieve the number?
 

=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)

"Eric" wrote in message
...
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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
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