3

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.

McHunkerson
  • 41
  • 6

2 Answers2

4

For a single cell try:

enter image description here

Formula in B1:

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

For the whole range:

enter image description here

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)))
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 2
    All formulas are linked now. this is really helpful =) – Mayukh Bhattacharya Jan 12 '23 at 11:31
  • Is the `@` in your TAKE/TEXTSPLIT solution to cope with if the TEXTSPLIT would spill 2D? – P.b Jan 12 '23 at 12:48
  • @P.b Yes that's correct. – JvdV Jan 12 '23 at 12:50
  • 1
    Oh I see what it does. It spills the last 2 column results without it. Adding @shows only the first of the 2. Very clever and clean solution. – P.b Jan 12 '23 at 12:53
  • 1
    @P.b, it's called implicit intersection. Adding the '@' will return the top-left element of an array. It's either that or a more verbose `TAKE()`. – JvdV Jan 12 '23 at 14:49
  • 1
    I know it works that way, but I am used to seeing that being used in ranges, so it returns the first. Basically it does exactly that, but in another context. – P.b Jan 12 '23 at 15:02
0

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.

McHunkerson
  • 41
  • 6