Google sheet need formula to go backward directory of URL link

google sheets

example i have data in colum

i need the formula that can go backward one or two directory in url link, so the result would be:

i read article about relative file paths:
Starting with "/" returns to the root directory
Starting with "../" moves one directory backwards Starting with "../../" moves two directories backwards

How to do that on google sheets formula or script, assume i have in A1:

so column B1: would be

in C1:

i need formula or script that can do that, please help, thanks you.

Best Answer

mmm, ok, (using formulas) first thing you need to remove the file name from the path, then identify how many "/" you got and count the characters so you know how many characters are from the prev-to-last "/", then use =left()-amount_o_chars and then add back the file name. I suggest you do things step by step. Each step in a different cell using the data from the previous one, once you achieve your goal, integrate the formulas into the last cell. It can be done, but you have to spend a long time playing with Find(), left(), right(), and any other text formula you can use.