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

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

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.

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

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

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