Editing a URL string

MSW

Hi

I need to modify a URL string. Should be a column formula.

The original string is

test.test.com/editor.html/content/folder1/folder2/folder3/folder4/catalog/section1/e/product.html

the result should be

test.com/folder1/folder2/folder3/folder4/section1/product.html

1. The beginning of the URL needs to be changed
2. /catalog/ needs to be removed
3. the folder /e/ needs to be removed. e can be any letter but is always 1 character long.

Any suggestions?

Paul Newcome

What about the "editor.html" portion near the beginning?

Can we use the first "." as a marker to start the new URL, or is it possible the initial URL could start with (for example) "test1.test2.test.com........." and we need to start at the second "."?

Will "catalog" always be "catalog" or could it be different text?

Marcel Staub

There won't be a case with two subdomains. The first part could be replaces 1:1 (till /en), then take out /catalog/ and take out the variable /e/. Folders after /en my vary in lenght.

author-eu-prod.test.com/editor.html/content/dia/master /en/products/catalog/params/e/product.html

dia.test.com/global /en/products/params/product.html

Paul Newcome

What we need to do is figure out some kind of commonality across all that we can leverage. We can't say to look for the (for example) fifth "/" and assume that the text between that one and the next one is the catalog because I imagine the number of folders is variable.

Removing the first bit is easy enough because we can just go to the first ".".

=SUBSTITUTE([URL Column]@row, LEFT([URL Column]@row, FIND(".", [URL Column]@row)), "")

After that though we need to figure something else out because there are so many variables.

Regarding the "catalog" you want to remove, is that always going to be the specific text of "catalog"?

.

Marcel Staub

Maybe from "the other side". The / after the "e" is always the last slash if the folder /catalog/ is in the URL. But then again /catalog/ needs to be removed...

Maybe if the folder before the last / is a 1 characher folder then remove the folder?

Running out of ideas :-) maybe it will remain a manual task...

Paul Newcome

Unfortunately there is no easy way to read from right to left in Smartsheet which means that even though the "/" after the "e" is the last one, when reading from left to right it can be rather difficult to pinpoint because of the variable text.

An option would be to include helper columns for each section you want to replace. Two for each. In one you enter the text you want to replace, and the other you enter the text you want to replace it with (or leave it blank).

From there we could use nested SUBSTITUTE functions to evaluate the original URL and basically use the helper columns to find/replace.

That might be easier than trying to edit the URL directly.