Extract part URL up to 2nd sub directory or file path

[SKooLZ]

Hi all, I have a list of SharePoint file URLs and I need you help with a formula to extract part of the URL up to the 2nd sub directory or sub-site e.g.

http://company.sharepoint.com/teamsite/Subdirectory1/
http://company.sharepoint.com/teamsite/Subdirectory1/folderA/folder1/
http://company.sharepoint.com/teamsite/Subdirectory1/folderB/
http://company.sharepoint.com/teamsite/Subdirectory2/
http://company.sharepoint.com/teamsite/Subdirectory2/folderC/
http://company.sharepoint.com/teamsite/Subdirectory3/
http://company.sharepoint.com/teamsite/Subdirectory3/folderD/
http://company.sharepoint.com/teamsite/Subdirectory3/folderE/folder2

The expected result I'm after on the right or left of a corresponding column to the above list, would be:

http://company.sharepoint.com/teamsite/Subdirectory1/
http://company.sharepoint.com/teamsite/Subdirectory1/
http://company.sharepoint.com/teamsite/Subdirectory1/
http://company.sharepoint.com/teamsite/Subdirectory2/
http://company.sharepoint.com/teamsite/Subdirectory2/
http://company.sharepoint.com/teamsite/Subdirectory3/
http://company.sharepoint.com/teamsite/Subdirectory3/
http://company.sharepoint.com/teamsite/Subdirectory3/


Caputuring just the first 2 sub directories which are: teamsite/subdirectoryX/

thanks in advance

 

[jtakw]

Hi,

Based on your description and works for your given samples:

Book1
	A	B
1	http://company.sharepoint.com/teamsite/Subdirectory1/	http://company.sharepoint.com/teamsite/Subdirectory1/
2	http://company.sharepoint.com/teamsite/Subdirectory1/folderA/folder1/	http://company.sharepoint.com/teamsite/Subdirectory1/
3	http://company.sharepoint.com/teamsite/Subdirectory1/folderB/	http://company.sharepoint.com/teamsite/Subdirectory1/
4	http://company.sharepoint.com/teamsite/Subdirectory2/	http://company.sharepoint.com/teamsite/Subdirectory2/
5	http://company.sharepoint.com/teamsite/Subdirectory2/folderC/	http://company.sharepoint.com/teamsite/Subdirectory2/
6	http://company.sharepoint.com/teamsite/Subdirectory3/	http://company.sharepoint.com/teamsite/Subdirectory3/
7	http://company.sharepoint.com/teamsite/Subdirectory3/folderD/	http://company.sharepoint.com/teamsite/Subdirectory3/
8	http://company.sharepoint.com/teamsite/Subdirectory3/folderE/folder2	http://company.sharepoint.com/teamsite/Subdirectory3/
Sheet372

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(SUBSTITUTE(A1,"/","/"&REPT(" ",LEN(A1)),5),LEN(A1)))

Formula copied down.

 

[SKooLZ]

Awesomeness Jtakw!! Much appreciated. It works perfectly but I realised I actually need it up to the 3rd subdirectory so I'm battling away trying to expand it. If you can help again, please do.

Thanks again.

 

[Fluff]

How about
=IFERROR(LEFT(A1,FIND("|",SUBSTITUTE(A1,"/","|",6))-1),A1)

 

[SKooLZ]

How about
=IFERROR(LEFT(A1,FIND("|",SUBSTITUTE(A1,"/","|",6))-1),A1)

Thanks Fluff, that is still behaving as the first formula and returning the 2nd subdirectory and not the 3rd?

 

[Fluff]

This is what I get

Excel 2013/2016
	A	B
1	http://company.sharepoint.com/teamsite/Subdirectory1/	http://company.sharepoint.com/teamsite/Subdirectory1/
2	http://company.sharepoint.com/teamsite/Subdirectory1/folderA/folder1/	http://company.sharepoint.com/teamsite/Subdirectory1/folderA
3	http://company.sharepoint.com/teamsite/Subdirectory1/folderB/	http://company.sharepoint.com/teamsite/Subdirectory1/folderB
4	http://company.sharepoint.com/teamsite/Subdirectory2/	http://company.sharepoint.com/teamsite/Subdirectory2/
5	http://company.sharepoint.com/teamsite/Subdirectory2/folderC/	http://company.sharepoint.com/teamsite/Subdirectory2/folderC
6	http://company.sharepoint.com/teamsite/Subdirectory3/	http://company.sharepoint.com/teamsite/Subdirectory3/
7	http://company.sharepoint.com/teamsite/Subdirectory3/folderD/	http://company.sharepoint.com/teamsite/Subdirectory3/folderD
8	http://company.sharepoint.com/teamsite/Subdirectory3/folderE/folder2	http://company.sharepoint.com/teamsite/Subdirectory3/folderE
data

Cell Formulas
Range	Formula
B1	=IFERROR(LEFT(A1,FIND("|",SUBSTITUTE(A1,"/","|",6))-1),A1)

 

[SKooLZ]

Hi Fluff...

You're right, I've realised that when copy it down by double clicking the bottom right of the cell, it copies and updates the cell references but the result displayed is the same as first cell. Only if I click in he next cell below and hit enter does it update to show correct value. Any ideas formatting that's causing? it's in a table.

 

[Fluff]

Sounds like you have calculation set to manual.
Either hit F9, or on the Formula Tab, Calculation options, change to Automatic

 

[SKooLZ]

Sorted, cleared all the column formatting and it's worked instantly, didn't even need to copy it down. Thanks a million guys. Save me work.

 

[Fluff]

For future reference
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Cross posted https://www.excelforum.com/excel-fo...url-up-to-2nd-sub-directory-or-file-path.html