Isolate string from filepath in Excel

Question

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

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

I tried:

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

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

and

Get parent folder path from file path using cell formula (came close isolating entire filepath above child)

but can't isolate the parent.

Answer 1

For a single cell try:

Formula in B1:

=@TAKE(TEXTSPLIT(A1,"/"),,-2)

For the whole range:

Formula in B1:

=TEXTAFTER("/"&TEXTBEFORE(A1:A4,"/",-1),"/",-1)

---

For older versions of Excel, I'd like to link to this Q&A about the use of FILTERXML() instead of a mixture of MID(), LEFT(), RIGHT() etc..

=IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(A1,"/","</s><s>")&"</s></t>","//s[last()-1]"),"")

If you are on an Excel version prior to 2013 and/or on Mac, then I think you'd rather be a little bit more dynamic then your own answer:

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",LEN(A1))),LEN(A1)*2),LEN(A1)))

Answer 2

Found the answer =TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",100)),200),100)) if it helps others. Works in Excel 2019 for Mac. Some solutions depend on the Excel version it appears.