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

Saqib Husain

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?

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

Rob Henson

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\

route217

ASKER

Then 6th Apologies error on my part

Rob Henson

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 \

route217

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

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Saqib Husain

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),""))))

Rob Henson

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.