Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.
Jul 04, 2019 08:58 AM
Hi all,
I’m a social media manager. I have a base which links together editorial content with associated social posts. It makes amazing things like this possible:
Now, I’m trying to create a fully automated UTM parameter system which collects data from various linked records — such as linked web content, associated campaign, etc — and spits out a single URL with appended UTM parameters for tracking in Google Analytics.
I’ve got most of it figured out. One step remains: how to extract part of a string. In this case, everything found AFTER the final “/” in a given field.
Here’s what I mean. I’d like a formula which scans this text, finds the final “/” in the string, then spits out everything AFTER that:
Here’s my current, incomplete formula:
Could I do this with RIGHT()
? Any help is much appreciated! Thanks in advance. :blush:
Best,
Alex
Jul 04, 2019 09:40 AM
Yes, it is possible, and, yes, you could do it with RIGHT()
. The problem comes in finding that last ‘/
’.
You don’t say how standard the URLs you are trying to parse are. (For instance, if they all come from the same website, you’d know how many '/
's you have to skip before FIND()
ing the last. Or if they come from a handful of sites, you could specify the [startFromPosition]
argument to FIND()
based on domain.)
However, if you are looking at a collection of arbitrary URLs with an unknown (but assumed maximum) number of embedded '/
's, then you’re looking at a more complex algorithm. Since Airtable currently does not support loop processing, you’ll have to count the number of '/
's in the URL and then craft a formula that steps through the URL a set number of times until the last ‘/
’ is located, and then plug that number into a MID()
or RIGHT()
function.
In this reply I give examples of a multi-branched formula to parse a collection of hashtags, which is structurally similar to a URL. Fortunately, once you have the basic formula down, extending it to support your assumed maximum number of '/
's is only annoying, not complicated. :winking_face: