Substring/Parsing Not sure what to call it!

[Takeadoe]

Data structure:

C:\Documents and Settings\tonkovic\My Documents\Data Files\Deer\Deer
Population Goals\Farmer Attitude Surveys\1995\Names and Addresses
\FRAN.ASC

The entire string is in a single field. The file name is "FRAN.ASC".
My hope is to find some way of parsing the field into 2 fields:
directory and file name. If I knew enough, my approach would be to
start from the far right and work my way left until I hit the first
"\". That would contain the file name and everything left would be
the directory.

Any help would be greatly appreciated!

Mike

 

[Rick Rothstein]

Data structure:

C:\Documents and Settings\tonkovic\My Documents\Data Files\Deer\Deer
Population Goals\Farmer Attitude Surveys\1995\Names and Addresses
\FRAN.ASC

The entire string is in a single field. The file name is "FRAN.ASC".
My hope is to find some way of parsing the field into 2 fields:
directory and file name. If I knew enough, my approach would be to
start from the far right and work my way left until I hit the first
"\". That would contain the file name and everything left would be
the directory.

Any help would be greatly appreciated!

Let's assume that text is in a variable named PathFileName, then you would
get the parts using the InStrRev function...

FileName = Mid(PathFileName, InStrRev(PathFileName, "\") + 1)

PathName = Left(PathFileName, InStrRev(PathFileName, "\"))

Rick Rothstein (MVP - Excel)

 

[Takeadoe]

Let's assume that text is in a variable named PathFileName, then you would
get the parts using the InStrRev function...

FileName = Mid(PathFileName, InStrRev(PathFileName, "\") + 1)

PathName = Left(PathFileName, InStrRev(PathFileName, "\"))

Rick Rothstein (MVP - Excel)

Rick - thank you so much! Unfortunately, it keeps telling me that
I've entered too few arguments for the function.

Not sure what to make of that.

Mike

 

[Takeadoe]

Rick - thank you so much!  Unfortunately, it keeps telling me that
I've entered too few arguments for the function.

Not sure what to make of that.

Mike- Hide quoted text -

- Show quoted text -

Should clarify - that message applies to the MID function. I get the
"#NAME?" with the LEFT function.

Sorry. I should have been clearer.

 

[Takeadoe]

If the string is in A1, then:

File Name:      =TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),99))

Directory:      =LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),99))))- Hide quoted text -

- Show quoted text -

My gosh! Thank you very much! How long did it take you to come up
with that? That's amazing! The only minor fix I would mention is that
the "\" that's hanging out there at the end of the directory "C\Deer
\Data\". Would it be too much to ask for assistance on removing the
"\" that terminates the directory name?

If it is a lot of work, don't sweat it.

Thanks again. I really appreciate the help.

Mike

 

[Rick Rothstein]

The only minor fix I would mention is that the "\" that's

hanging out there at the end of the directory "C\Deer
\Data\". Would it be too much to ask for assistance on
removing the "\" that terminates the directory name?

To do that, all we need to do is subtract 1 from the 2nd argument for the
LEFT function...

=LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),99)))-1)

Oh, and just to follow up on your comments you made to my original
message... the answer I gave you was a VB solution, not a worksheet formula
solution.

Rick Rothstein (MVP - Excel)

 

[Takeadoe]

To do that, all we need to do is subtract 1 from the 2nd argument for the
LEFT function...

=LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),99)))-1)

Oh, and just to follow up on your comments you made to my original
message... the answer I gave you was a VB solution, not a worksheet formula
solution.

Rick Rothstein (MVP - Excel)

Rick - You're a life saver! Thank you. That worked perfectly.
However, it snagged me a bit at first. I didn't immediately notice
that you added the -1 inside the ) on the far right. I thought you
added it. That caused some problems until I figured out what you did.

Thanks again. Sorry about the confusion early. I don't know VBA
well.

Mike