Forum Discussion

johnjohn-Peter's avatar
johnjohn-Peter
Iron Contributor
Oct 13, 2025

Remove sub-folder urls if the parent folder url exists

I have an excel sheet with one column which contain urls from sharepoint, the urls looks as follow:-

 

/sites/Marketing/Budget

/sites/Marketing/Budget/2015

/sites/Marketing/Budget/206

/sites/maps

/sites/Expesnes

/sites/Expenses/2020

 

now i want to delete all the sub-folders if their parent folder exists? so for example the above rows should be as follow:-

/sites/Marketing/Budget

/sites/maps

/sites/Expesnes

 

is there any script i can run to do this cleanup?

 

Thanks

 

 

admin
developer
excel
Excel for web
Formulas and Functions

2 Replies

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Oct 13, 2025

    Excel formula approach.

    =UNIQUE(LET(x,REGEXREPLACE(A2.:.A5000,"[0-9]",""),IF(RIGHT(x,1)="/",LEFT(x,LEN(x)-1),x)))

    Sample File

     

    • m_tarler's avatar
      m_tarler
      Bronze Contributor
      Oct 14, 2025

      I like Harun24HR approach using REGEX but that will only remove 1 layer of subfolders and only subfolders that are numbers.  Here is a more general solution but I resorted to SCAN function:

      =LET(in,$A$2:.$A$99,I,SORT(in),UNIQUE(SCAN(TAKE(I,1),DROP(I,1),LAMBDA(p,q,IF(ISNUMBER(SEARCH(p,q)),p,q)))))

       

Resources