Text Functions
 

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,(SE ARCH("\",Path,(SEARCH("X$",Path,1))+3))+1))),"\\SE RVER\X$\",""),SUBSTITUTE(LEFT(Path,(SEARCH("\",Pat h,(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 :o)

Text Functions
 

Youre quite right the wrong ones a

\\SERVER\X$\
HLD: #VALUE! < N/A < SubDir: N/A €“WRONG

What I was trying to see was:
HLD: N/A < N/A < SubDir: N/A

and

\\SERVER\X$\$Extend\Test
HLD: $Extend\ < N/A < SubDir: t €“WRONG

Aiming for:
HLD: $Extend\Test < N/A < SubDir: N/A

i.e. Im trying to do ...
from the source string, say ...
\\SERVER\X$\data\Team Name\Personal Data\UserName\mail\
Im trying to ignore the server and drive,

Take the next 2 directories as HLD (High Level Directory). If there's only
one directory after the server/drive use that as HLD

The next 1 as Directory

Remaining directories as sub-directories

HLD = data\Team Name\
Directory = Personal Data
Sub-Directories = \UserName\mail\

Text Functions
 

Thanks. That worked a treat ... Think I'll have to get into functions as
they're easier to read :o)