Extracting text from between consistent characters

[Brian_Swichkow]

I’m trying to grab a variable amount of text from between two consistent characters, > and <, to format HTML. I’m using Zapier to convert rich text to HTML and then using an Airtable formula to modify the URLs with UTM codes. Example below.

Starting Text
Cooperative Currency

Desired Output
Cooperative Currency

From this
">Cooperative Currency<

To this
Cooperative%20Currency">Cooperative Currency<

Thank you!

~Brian

[Justin_Barrett]

Welcome to the community, @Brian_Swichkow! I think the forum formatting might be messing up your example text. It looks like some characters are missing from some examples, and others (URLs?) have been turned into live links, so we can’t see the URL text. I suggest editing your post and wrapping graves around any raw text. A grave is the “styled apostrophe” that shares the key with the tilde (~) symbol on most keyboards.

Before:
Line of text

After:
`Line of text`

Displays as:
Line of text

This should render your URLs as the text of the URL, and possibly fix the other examples as well.

[Brian_Swichkow]

Thank you! Yes! Here’s the desired change:

@Brian_Swichkow

">Cooperative Currency<

To this
Cooperative%20Currency">Cooperative Currency<

Here’s the raw text:

<a href="https://mythos.one/brianswichkow/7f2fd4?utm_source=resonance%20machine&utm_medium=email&utm_campaign=Resonance%20Machine&utm_term=">Cooperative Currency</a>

Change to this:

<a href="https://mythos.one/brianswichkow/7f2fd4?utm_source=resonance%20machine&utm_medium=email&utm_campaign=Resonance%20Machine&utm_term=Cooperative%20Currency">Cooperative Currency</a>

[Justin_Barrett]

Thanks for the clarification. My personal preference is to do this in two fields, just to keep the formulas somewhat manageable and to reduce redundant code. First, the formula that extracts the text. I named this field {Extracted}, and assumed the URL is in a similarly-named field:

IF(URL, TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(URL, "</a>", ""), ">", REPT(" ", 100)), 100)))

Then the formula that re-builds the URL using that extracted piece:

IF(URL, LEFT(URL, FIND('">', URL) - 1) & ENCODE_URL_COMPONENT(Extracted) & '">' & Extracted & "</a>")

If you would prefer to have everything in a single field, here’s the mashup of those two into a single mega-formula:

IF(URL, LEFT(URL, FIND('">', URL) - 1) & ENCODE_URL_COMPONENT(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(URL, "</a>", ""), ">", REPT(" ", 100)), 100))) & '">' & TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(URL, "</a>", ""), ">", REPT(" ", 100)), 100)) & "</a>")

[Brian_Swichkow]

Thank you for the fast, cogent, and exceptionally detailed response Justin!

Seeing this, and playing with it now, I forgot to add one important clarifier… it’s an HTML field, not a singular URL. My early attempts were using formula elements that seem for single instances.

For context, we’re taking (1) a rich text field submitted via form view, (2) converting the markup to HTML with Zapier, (3) adding tracking codes, and (4) sending with SendGrid.

[Justin_Barrett]

@Brian_Swichkow

I forgot to add one important clarifier… it’s an HTML field, not a singular URL.

That’s indeed a very important clarifier. The URL in your screenshot is missing all of the UTM codes, though. Are you trying to add just the one UTM code to each of those URLs based on their respective text, or do the whole collection of UTM codes from the earlier example need to be added?

In a way, I think you’re making more work for yourself by trying to take this HTML—which, if I read you correctly, is the output of a zap in Zapier—and retrofit it with the UTM codes. It would be much easier to create the pieces you need for the full URL in some helper fields based on the form submission, and have Zapier build the full HTML—including the full URL—using those pieces. Does that make sense?

[Brian_Swichkow]

Thanks for these questions, Justin. And for the extraordinary detail, you continue to provide fantastic reflections of this process and it seems apt to explain the flow in which this is embedded.

We have (1) an Artable form with a rich text field that collects the contents of an email to be sent, (2) a zap that converts that rich text to HTML, and (3) this intended formula for adding a UTM code to each URL within the submitted text (the number of URLs per message varies greatly).

For us, the most important element of the UTM is the term= (adding what’s between ">TEXT</a> to the end of the UTM) as this is what shows up in our real-time viewer.

For example, this HTML with two URLs:

<p>Let's sync at <a href="https://mythos.one/brianswichkow/7f2fd4">Cooperative Currency</a>.</p><p><a href="https://mythos.one/brianswichkow/0b480c">John Zdanowski</a> is hosting the space at 3:45 PM today.</p>

Would become this HTML with UTMs added to each:

<p>Let's sync at <a href="https://mythos.one/brianswichkow/7f2fd4?utm_source=BrianPlus&utm_medium=Email&utm_campaign=One%20Gathering&utm_term=Cooperative%20Currency">Cooperative Currency</a>.</p><p><a href="https://mythos.one/brianswichkow/0b480c?utm_source=BrianPlus&utm_medium=Email&utm_campaign=One%20Gathering&utm_term=John%20Zdanowski">&#x1f4dd;John Zdanowski</a> is hosting the space at 3:45 PM today.</p>

@Justin_Barrett

It would be much easier to create the pieces you need for the full URL in some helper fields based on the form submission, and have Zapier build the full HTML—including the full URL—using those pieces.

We do this elsewhere. At present, I’m not seeing how to (a) multiple links of an unknown quantity from the source text or HTML and (b) add the term= field of the UTM using the linked text.

Turning

">THIS TEXT</a>

Into

&utm_term=THIS%20TEXT">THIS TEXT</a>

Thank you so much again for your help.

~Brian

[Justin_Barrett]

Thanks for the further clarification, Brian. That confirms what I suspected based on that most recent screenshot you posted. The biggest problem is this:

@Brian_Swichkow

multiple links of an unknown quantity

Unfortunately Airtable doesn’t have any support in its formula fields for operating on arbitrary quantities of things. It’s great with fixed quantities, but once it becomes variable, it becomes a lot more difficult to process in a formula, especially if each instance needs unique treatment.

My next suggestion is to look into writing a custom script in the Scripting block. Those scripts are written in JavaScript, which has much more robust features for text parsing and iteration through arbitrary collections. The downside is that you would need to manually run this block whenever you’ve got new records that haven’t been tweaked, but it could be made smart enough to only process each record once, skipping over those that already have the UTM info added.

If you’d like assistance with writing code for the Scripting block, I suggest posting in the #developers:work-offered category, perhaps linking back to this post so people can see what needs to be done. (I would offer my own services, but my JavaScript is still a tad rusty, and there are definitely folks here who would get you the code you need much faster than I could. )

[Brian_Swichkow]

Thank you Justin!

I’ve just posted that request here.

We, along with the former CFO of Second Life, have built an economy on Airtable. We were in the beta for scripting blocks, but other projects took priority. Excited and terrified (by legacy code) to dive into the scripting block. Thank you so much again!

~Brian