| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to retrieve number from filename? | Excel Discussion (Misc queries) | |||
| How do I retrieve a page number in Excel? | Excel Worksheet Functions | |||
| Retrieve Row index number | Excel Discussion (Misc queries) | |||
| Retrieve multiple dates, from a week number? | New Users to Excel | |||
| Retrieve a number value from a word | Excel Discussion (Misc queries) | |||
Linear Mode
