Isolate string from filepath

[howster1976]

Hi,
Let's say I have 4 rows in Column A (titled filepaths) with the following filepath content:

A1: /page1/page2/page3
A2: /page4/page5
A3: /page6/page7/page8/page9
A4: /page10
...
A300 .....

...I need a formula to isolate the parent of the last child into eg the corresponding column B (titled parent), so:

B1 would derive a value of page2
B2 would derive a value of page4
B3 would derive a value of page8
B4 would derive a value of [blank] as it has no parent

Can anyone suggest a formula please that could achieve this?

Thanks

 

[aaewalsh]

=IF(LEN(A1) = LEN(SUBSTITUTE(A1,"/","")),"",MID(A1,FIND(REPT("/",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1),LEN(A1)))

This formula checks if the cell in Column A has any slashes, and if so, it uses the FIND and MID functions to extract the text after the second to last slash. If the cell has no slashes, it returns an empty string.

You can then copy and paste this formula to the corresponding cells in Column B.

 

[Georgiboy]

Depends on what Excel version as well, i would use the below for the latest version of Excel, there will always be other ways to do it.

=LET(v,A1,INDEX(TEXTSPLIT(v,"/"),LEN(v)-LEN(SUBSTITUTE(v,"/",""))))

 

[howster1976]

=IF(LEN(A1) = LEN(SUBSTITUTE(A1,"/","")),"",MID(A1,FIND(REPT("/",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1),LEN(A1)))

This formula checks if the cell in Column A has any slashes, and if so, it uses the FIND and MID functions to extract the text after the second to last slash. If the cell has no slashes, it returns an empty string.

You can then copy and paste this formula to the corresponding cells in Column B.

Thanks I'm running Excel v16.68 for Mac (2019 ish) but when I try that formula I get "You've entered too few arguments for this function."
I have your formula in B1
Cell A1 has "/page1/page2" and I'm hoping to get "page2" as the result in cell B1
Am I doing this right?
Thanks

 

[howster1976]

Depends on what Excel version as well, i would use the below for the latest version of Excel, there will always be other ways to do it.

=LET(v,A1,INDEX(TEXTSPLIT(v,"/"),LEN(v)-LEN(SUBSTITUTE(v,"/",""))))

Thanks I'm running Excel v16.68 for Mac (2019 ish) but when I try that formula I get "#NAME?"
I have your formula in B1
Cell A1 has "/page1/page2" and I'm hoping to get "page2" as the result in cell B1
Am I doing this right?
Thanks

 

[howster1976]

=IF(LEN(A1) = LEN(SUBSTITUTE(A1,"/","")),"",MID(A1,FIND(REPT("/",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1),LEN(A1)))

This formula checks if the cell in Column A has any slashes, and if so, it uses the FIND and MID functions to extract the text after the second to last slash. If the cell has no slashes, it returns an empty string.

You can then copy and paste this formula to the corresponding cells in Column B.

I get this error in my version of Excel. Thanks

 

[howster1976]

Depends on what Excel version as well, i would use the below for the latest version of Excel, there will always be other ways to do it.

=LET(v,A1,INDEX(TEXTSPLIT(v,"/"),LEN(v)-LEN(SUBSTITUTE(v,"/",""))))

I get this error unfortunately. Thanks

 

[Georgiboy]

My version will not work for you as it uses functions that are not available to your version of Excel.

 

[Peter_SSs]

I'm running Excel v16.68 for Mac (2019 ish)

Please put your version info into your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Here is another one to try for your version.

23 01 12.xlsm
	A	B
1	/page1/page2/page3	page2
2	/page4/page5	page4
3	/page6/page7/page8/page9	page8
4	/page10
From path

Cell Formulas
Range	Formula
B1:B4	B1	=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",100)),200),100))

 

[howster1976]

Please put your version info into your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Here is another one to try for your version.

23 01 12.xlsm
	A	B
1	/page1/page2/page3	page2
2	/page4/page5	page4
3	/page6/page7/page8/page9	page8
4	/page10
From path

Cell Formulas
Range	Formula
B1:B4	B1	=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",100)),200),100))

Outstanding works perfectly!