Editing a URL string
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
- The beginning of the URL needs to be changed
- /catalog/ needs to be removed
- the folder /e/ needs to be removed. e can be any letter but is always 1 character long.
Any suggestions?
Answers
-
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?
-
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
-
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"?
.
-
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...
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!