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


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

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

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 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

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