C
Charlie
I'm trying to split a text string ... Apart from it not working quite right
yet! I'm sure I've over complicated it.
Base Data "Path":
\\SERVER\X$\data\Team Name\Personal Data\JOE BLOGGS\mail\
\\SERVER\X$\
\\SERVER\X$\RECYCLER\
\\SERVER\X$\$Extend\
\\SERVER\X$\$Extend\Test
I'm trying to split into High Level Directory (HLD) / Team/User Directory
(TUD) / Team/User Sub-Directory
HLD:
=IFERROR(SUBSTITUTE(LEFT(Path,(SEARCH("\",Path,(SEARCH("\",Path,(SEARCH("X$",Path,1))+3))+1))),"\\SERVER\X$\",""),SUBSTITUTE(LEFT(Path,(SEARCH("\",Path,(SEARCH("\",Path,(SEARCH("X$",Path,1))))+1))),"\\SERVER\X$\",""))
Directory:
=IFERROR(LEFT(SUBSTITUTE(SUBSTITUTE(Path,"\\SERVER\X$\",""),HLD,""),
SEARCH("\",SUBSTITUTE(SUBSTITUTE(Path,"\\SERVER\X$\",""),HLD,""),1)-1),"N/A")
Sub-Directory:
=IFERROR(RIGHT(Path,(LEN(Path)-LEN(HLD))-LEN(TUD)-LEN("\\SERVER\X$\")),"N/A")
Results:
HLD: data\Team Name\ <> TUD Personal Data <> SubDir: \JOE BLOGGS\mail\ --OK
HLD: #VALUE! <> N/A <> SubDir: N/A --WRONG
HLD: RECYCLER\ <> N/A <> SubDir: N/A --OK
HLD: $Extend\ <> N/A <> SubDir: N/A --WRONG
HLD: $Extend\ <> N/A <> SubDir: t –WRONG
It works with most of the data, but not when I get to root files!
Any suggestions would be welcome )
yet! I'm sure I've over complicated it.
Base Data "Path":
\\SERVER\X$\data\Team Name\Personal Data\JOE BLOGGS\mail\
\\SERVER\X$\
\\SERVER\X$\RECYCLER\
\\SERVER\X$\$Extend\
\\SERVER\X$\$Extend\Test
I'm trying to split into High Level Directory (HLD) / Team/User Directory
(TUD) / Team/User Sub-Directory
HLD:
=IFERROR(SUBSTITUTE(LEFT(Path,(SEARCH("\",Path,(SEARCH("\",Path,(SEARCH("X$",Path,1))+3))+1))),"\\SERVER\X$\",""),SUBSTITUTE(LEFT(Path,(SEARCH("\",Path,(SEARCH("\",Path,(SEARCH("X$",Path,1))))+1))),"\\SERVER\X$\",""))
Directory:
=IFERROR(LEFT(SUBSTITUTE(SUBSTITUTE(Path,"\\SERVER\X$\",""),HLD,""),
SEARCH("\",SUBSTITUTE(SUBSTITUTE(Path,"\\SERVER\X$\",""),HLD,""),1)-1),"N/A")
Sub-Directory:
=IFERROR(RIGHT(Path,(LEN(Path)-LEN(HLD))-LEN(TUD)-LEN("\\SERVER\X$\")),"N/A")
Results:
HLD: data\Team Name\ <> TUD Personal Data <> SubDir: \JOE BLOGGS\mail\ --OK
HLD: #VALUE! <> N/A <> SubDir: N/A --WRONG
HLD: RECYCLER\ <> N/A <> SubDir: N/A --OK
HLD: $Extend\ <> N/A <> SubDir: N/A --WRONG
HLD: $Extend\ <> N/A <> SubDir: t –WRONG
It works with most of the data, but not when I get to root files!
Any suggestions would be welcome )