Parse File Location

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
One way:

=MID(A1, FIND("$$", SUBSTITUTE(A1, "\", "$$", LEN(A1) -
LEN(SUBSTITUTE(A1, "\", "")))) + 1, 255)

Where 255 is just a large number
 
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.
 
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 Edit>Copy and Edit>Paste Special > Values ( so that
the new addresses will be values instead of formulae)
iv) Parse
 
Thanks!

JE McGimpsey said:
One way:

=MID(A1, FIND("$$", SUBSTITUTE(A1, "\", "$$", LEN(A1) -
LEN(SUBSTITUTE(A1, "\", "")))) + 1, 255)

Where 255 is just a large number
Click to expand...
 
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: Data>Text 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.
 
Back
Top