If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 ) |
#2
|
|||
|
|||
Text Functions
You’re 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. 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\ |
#3
|
|||
|
|||
Text Functions
Thanks. That worked a treat ... Think I'll have to get into functions as
they're easier to read ) |
Thread Tools | |
Display Modes | |
|
|