Extracting text from between consistent characters

Welcome to the community, @Brian_Swichkow! :grinning_face_with_big_eyes: 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.

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

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>

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>")

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.

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?

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>

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

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