Skip to main content

Extracting partial text string following a specific character

  • July 4, 2019
  • 2 replies
  • 164 views
A

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

2 replies

W
Forum|alt.badge.img+5
  • W_Vann_Hall
  • Inspiring
  • July 4, 2019

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:

A
A
O
Forum|alt.badge.img+2
  • olausenn
  • Participating Frequently
  • December 21, 2023

Hi, 

I came here searching for an answer to this exact question, how to extract a string that follows the last instance of a certain character. In my case, I have a field containing a string like "TB2023-12-19-9-R-BB26", and I want to extract everything after the last "-", in this case "BB26". I could use a RIGH() function to extract the last four characters, but since it is based on a "count" field, the length will increase with time. Therefore, I first need to find how many characters there are after the last "-" and then extract that number from the string. 

Here is my solution in a single code: 

RIGHT({Field}, LEN({Field}) - FIND("}", SUBSTITUTE({Field}, "-", "}", LEN({Field}) - LEN(SUBSTITUTE({Field}, "-", "")))) )

 

Works like a charm! 😄 

J
Terms & ConditionsAccessibility statement