Text Functions

[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 )

 

[p45cal]

what makes:
HLD: RECYCLER\ <> N/A <> SubDir: N/A
OK
but:
HLD: $Extend\ <> N/A <> SubDir: N/A
WRONG?

Please add what you'd want to see as results for the three 'wrong' one
and give some guide as to what rules you apply to decide what's what.

 

[Charlie]

You’re quite right the wrong ones are:

\\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. I’m trying to do ...
from the source string, say ...
\\SERVER\X$\data\Team Name\Personal Data\UserName\mail\
I’m 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\

 

[p45cal]

I started getting tied up in knots trying to adjust long formulae like
yours so I went down the user-defined function route so that the
formulae on the sheet look like:
=Dirs($C2,"X$","HLD")
=Dirs($C2,"X$","TUD")
=Dirs($C2,"X$","sub")

where C2 contains the full path, "X$" is the disk drive, which you used
in your formulae, and 'hld', 'tud', 'sub' the part you want to see.
Upper/lower case doesn't matter.

This is supported by a UDF in a code module:


VBA Code:
--------------------


Function Dirs(path, id, part) As String
Dirs = "Error"
Start = InStr(1, path, id, vbTextCompare)
If Start = 0 Then
Dirs = id & " not found"
Exit Function
End If
Start = Start + Len(id) + 1
myStr = Mid(path, Start)
HLD = "N/A": TUD = "N/A": mySub = "N/A"
If Len(myStr) = 0 Then: Dirs = "": Exit Function
xxx = Split(myStr, "\", 4, vbTextCompare)
If UBound(xxx) = 0 Then HLD = xxx(0)
If UBound(xxx) > 0 Then HLD = xxx(0) & "\" & xxx(1)
If Right(HLD, 1) = "\" Then HLD = Left(HLD, Len(HLD) - 1)
If UBound(xxx) > 1 Then TUD = xxx(2)
If Right(TUD, 1) = "\" Then TUD = Left(TUD, Len(TUD) - 1)
If UBound(xxx) > 2 Then mySub = xxx(3)
If Right(mySub, 1) = "\" Then mySub = Left(mySub, Len(mySub) - 1)
Select Case UCase(part)
Case "HLD": Dirs = HLD
Case "TUD": Dirs = TUD
Case "SUB": Dirs = mySub
Case Else: Dirs = "part '" & part & "' not valid"
End Select
End Function

 

[Charlie]

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