Forum Discussion
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/map...
Excel formula approach.
=UNIQUE(LET(x,REGEXREPLACE(A2.:.A5000,"[0-9]",""),IF(RIGHT(x,1)="/",LEFT(x,LEN(x)-1),x)))
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)))))