| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to retrieve data from other filename? | Excel Discussion (Misc queries) | |||
| Retrieve filename question, again... | New Users to Excel | |||
| Number gets added to filename | Excel Discussion (Misc queries) | |||
| Retrieve Row index number | Excel Discussion (Misc queries) | |||
| number "1" added to filename | Excel Discussion (Misc queries) | |||
Linear Mode
