asked on Extract string from long file path name
Hi Experts
Hi Experts
How would you extract the following String "DevelopmentTeam" from the below file path into a new cell as show as Development Team - with Space from the following
'\\global.lloydstsb.com\file\d_pc_ds0002$\@GMT-2001.05.16-23.01.00\Shared\DevelopmentTeam\Live App Source Code\DR100 - DR199
Hi Experts
How would you extract the following String "DevelopmentTeam" from the below file path into a new cell as show as Development Team - with Space from the following
'\\global.lloydstsb.com\file\d_pc_ds0002$\@GMT-2001.05.16-23.01.00\Shared\DevelopmentTeam\Live App Source Code\DR100 - DR199
Microsoft ExcelMicrosoft Office
Last Comment
Rob Henson
ASKER
Always after Shared? Yes
Always before Live App Source Code? No Dynamic
Always the 5th block? Yes
Always the third block from the end? No Dynamic could increase or decrease.
Any other criteria? nothing else i can think of
Always before Live App Source Code? No Dynamic
Always the 5th block? Yes
Always the third block from the end? No Dynamic could increase or decrease.
Any other criteria? nothing else i can think of
Always the 5th block? Yes
But DevelopmentTeam is section 6 ???
\\global.lloydstsb.com
\file
\d_pc_ds0002$
\@GMT-2001.05.16-23.01.00
\Shared
\DevelopmentTeam\
But DevelopmentTeam is section 6 ???
\\global.lloydstsb.com
\file
\d_pc_ds0002$
\@GMT-2001.05.16-23.01.00
\Shared
\DevelopmentTeam\
ASKER
Then 6th Apologies error on my part
Is there other values that could occur in that section?
Just wondering how to determine the "Team" name in order to insert the space into it.
The result will be the string between the 7th and 8th occurrences of \
Just wondering how to determine the "Team" name in order to insert the space into it.
The result will be the string between the 7th and 8th occurrences of \
ASKER
Ignore the space between DevelopmentTeam as per your second point...
and
The result will be the string between the 7th and 8th occurrences of \ - Yes
and
The result will be the string between the 7th and 8th occurrences of \ - Yes
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
I also managed to enter the space.
If you have Office subscription and office insider then you can use
=TRIM(TEXTJOIN("",,LET(st,MID(A2,FIND("#",SUBSTITUTE(A2,"\","#",7))+1,FIND("#",SUBSTITUTE(A2,"\","#",8))-FIND("#",SUBSTITUTE(A2,"\","#",7))-1),IFERROR(IF(CODE(MID(st,ROW(A1:A100),1))<97," ","")&MID(st,ROW(A1:A100),1),""))))
If you have Office subscription and office insider then you can use
=TRIM(TEXTJOIN("",,LET(st,MID(A2,FIND("#",SUBSTITUTE(A2,"\","#",7))+1,FIND("#",SUBSTITUTE(A2,"\","#",8))-FIND("#",SUBSTITUTE(A2,"\","#",7))-1),IFERROR(IF(CODE(MID(st,ROW(A1:A100),1))<97," ","")&MID(st,ROW(A1:A100),1),""))))
Just thought how to determine the "Team" name in order to insert the space into it.
If you have a table of Team Names without the spaces and their equivalent with a space, you can do a lookup of the name without space and return the name with space.
If you have a table of Team Names without the spaces and their equivalent with a space, you can do a lookup of the name without space and return the name with space.
Is it
Always after Shared?
Always before Live App Source Code?
Always the 5th block?
Always the third block from the end? or
Any other criteria?