Link to home
Create AccountLog in
Avatar of route217
route217Flag for United Kingdom of Great Britain and Northern Ireland

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
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Need the criteria to define this.
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?
Avatar of route217

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 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\
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 \
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

ASKER CERTIFIED SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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),""))))


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.