Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parse File Location
Hello,
I would like to parse the following to extract just the file name: C:\MICTSeed\MICTSeeding.mdb. The file name will be the same but the location can change so how do I account for this when creatig my formula. Would it be better to do this using VBA? Thanks, Mike |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parse File Location
One way:
=MID(A1, FIND("$$", SUBSTITUTE(A1, "\", "$$", LEN(A1) - LEN(SUBSTITUTE(A1, "\", "")))) + 1, 255) Where 255 is just a large number In article , Mike wrote: Hello, I would like to parse the following to extract just the file name: C:\MICTSeed\MICTSeeding.mdb. The file name will be the same but the location can change so how do I account for this when creatig my formula. Would it be better to do this using VBA? Thanks, Mike |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parse File Location
If your file name is in cell A1 this will work:
=RIGHT(A1,LEN(A1)-FIND("?",SUBSTITUTE(A1,"\","?",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))) Split down it does this: 1. Find the length of the full path 2. Remove the \ from the path and find the length of the string. 3. The difference is how many \'s are in the string. 4. Replace the last \ with a character that won't appear in a filename. 5. Cut off everything to the right of this. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parse File Location
Mike,
As the length of the file names vary, I feel you can achieve your goal by substituting the delimiters. I believe, only "\" and "." are going to be the delimiters. If so,try this: --------- i)Go to a column adjacent to the file address. ii)Use "Substitute " function [ e.g., =Substitute(D9,".","\")], D9 is the cell in which the address is there. This will substitute all dots with slashes. iii) In the new cells EditCopy and EditPaste Special Values ( so that the new addresses will be values instead of formulae) iv) Parse -------- Pl Keep me informed. "Mike" wrote: Hello, I would like to parse the following to extract just the file name: C:\MICTSeed\MICTSeeding.mdb. The file name will be the same but the location can change so how do I account for this when creatig my formula. Would it be better to do this using VBA? Thanks, Mike |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parse File Location
Thanks!
"JE McGimpsey" wrote: One way: =MID(A1, FIND("$$", SUBSTITUTE(A1, "\", "$$", LEN(A1) - LEN(SUBSTITUTE(A1, "\", "")))) + 1, 255) Where 255 is just a large number In article , Mike wrote: Hello, I would like to parse the following to extract just the file name: C:\MICTSeed\MICTSeeding.mdb. The file name will be the same but the location can change so how do I account for this when creatig my formula. Would it be better to do this using VBA? Thanks, Mike |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parse File Location
Mike, in my response, I missed one thing. I forgot that you may need the file
extension also after parsing. If so the job will be simpler. Just select the cells containing the addresses and do: DataText To Columns Delimited . Press Next In the "Delimiters" select "Other" Type \ and press "Next" and "Finish". Ensure that adjacent cells are vacant to accommodate the parsed data. "Mike" wrote: Hello, I would like to parse the following to extract just the file name: C:\MICTSeed\MICTSeeding.mdb. The file name will be the same but the location can change so how do I account for this when creatig my formula. Would it be better to do this using VBA? Thanks, Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
file location | Excel Discussion (Misc queries) | |||
Get File location | Excel Discussion (Misc queries) | |||
Location of XLB file? | Excel Discussion (Misc queries) | |||
Import Pipe Delimited File, Parse out certian Fields, create new f | New Users to Excel | |||
Location of Temp File(s) - If any | Excel Discussion (Misc queries) |