Split function to get a relative path
 

I have a full file path and I want to extract only part of the path
using the VBA split function.

For example, in C:\windows\excel\archives\blah.xls I want to extract
"\excel\archives" only.

I have the C:\windows already in another cell. So basically I just
want to cut off this part as well as the file name to give me my
relative path. Any suggestions?

Split function to get a relative path
 

=SUBSTITUTE(LEFT(J1,FIND("~",SUBSTITUTE(J1,"\","~" ,LEN(J1)-LEN(SUBSTITUTE(J1
,"\",""))))-1),G1,"")

assuming path in J1, C:\Windows\ in G1, note trailing \.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jon" wrote in message
oups.com...
I have a full file path and I want to extract only part of the path
using the VBA split function.

For example, in C:\windows\excel\archives\blah.xls I want to extract
"\excel\archives" only.

I have the C:\windows already in another cell. So basically I just
want to cut off this part as well as the file name to give me my
relative path. Any suggestions?