Dim sFile As String
Dim iPos As Long
Dim ipos2 As Long

sFile =
"P:\firstfolder\secondfolder\thirdfolder\fourthfol der\fifthfolder\filename"

iPos = InStr(InStr(InStr(sFile, "\") + 1, sFile, "\") + 1, sFile, "\")
Range("A1").Value = Left(sFile, iPos)
ipos2 = InStrRev(sFile, "\")
Range("A2").Value = Mid(sFile, iPos + 1, ipos2 - iPos)
Range("A3").Value = Right(sFile, Len(sFile) - ipos2)


--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"dd" <dd.dd wrote in message
...
I have a string which will contain strings of varying lengths which
contain
the path to a file.
For example:
P:\firstfolder\secondfolder\thirdfolder\fourthfold er\fifthfolder\filename
The root of the path for this example is P:\firstfolder\secondfolder\
In reality the folder names are all varying lengths.

The code loops through all folders in a specific root to find a filetype.
It presently puts the path after the root in one column and the filename
in
the next.
The filename column contains hyperlinks by adding the root, path and file
together.

I want the path to be split so that the third folder is put in Column A,
the
remaining path is put in the Column B and the filename in Column C. I want
to alter the existing code to do this.

I want to use Mid(String,Start,Length) to find the Length of the string
for
the first folder in the path after the root (thirdfolder) and before
fourth
folder.

Does anyone know the syntax I should use?
Or, is there an easier alternative?

DDAwson
Scotland