How do I use a script to pull variables out of long text to populate multiple fields?

[David_Needham]

Hi all! I’m new to Airtable and trying to figure out how to pull multiple variables out of a single long text field.

I’m pulling the Description from an event in Google Calendar and syncing it into a Description field in Airtable. This field will have some things defined in a predictable pattern, such as the Zoom URL, name of the presenter, etc. - each corresponding to a field that exists in Airtable. What is the best way to set this up to be parsed through in Airtable? Is there a tutorial out there that could point me in the right direction?

Feel free to suggest something else if I’m going about this the wrong way. Thank you!

[Vivid-Squid]

Hi @David_Needham ,
Take a look at the Formula documentation on FIND() for text: https://support.airtable.com/hc/en-us/articles/203255215-Formula-Field-Reference#text_functions

You will need to do a series of formulas to get everything.

One thing I do to create long formulas is to break out each item in the process into its own formula field. Then once you get the desired result, you can copy the formulas into one big formula.

[Justin_Barrett]

You might also experiment with regular expressions and Airtable’s Regex functions. For complex text extraction, I find those much easier to work with, though it does take some practice to know how to write the expression. For testing, I recommend a site like regex101.com (make sure to change to the “Golang” variant, which is closest to what Airtable uses for its Regex parser).

[David_Needham · Accepted solution]

Thank you @Vivid-Squid & @Justin_Barrett ! This was really helpful. It took a couple of formula fields and some automations, but here’s what it looks like:

–

1. In the Gcal event I have something like this in the Description field:

Workshop: My Workshop Name
Zoom URL: https://zoom.com/s/something-here

2. Thanks to an automation, that field syncs from Gcal to Airtable into a record that I named Description.

3. Then I have a formula field named “Workshop Name” with the following code: REGEX_EXTRACT(Description,'Workshop: (.*?)<'). The < works because Gcal converts the line break into a <br> and occasionally will add unnecessary html formatting that I don’t want.

4. Thanks to another automation, my Workshop link field (referencing the Workshop table) is updated to match the newly cleaned up Workshop Name. (Tangent: I initially had trouble with my Workshop table getting filled with garbage as I debugged this, but I used this tip and made the primary field in the Workshop table a formula so that it could not be added to if what is defined in the “Workshop Name” does not exist in the Workshop table.)

5. Finally, I also have a formula field “Zoom URL” with the following code: REGEX_EXTRACT(Description,'Zoom URL: <a href="(.*?)"'). This works because Gcal converts the link into html anchor tags.

–

All-in-all, the hardest part was figuring out the right regex syntax, but that link you shared helped a lot with that. Thanks again!

[David_Needham]

Since I didn’t actually need a scripting app, this doesn’t seem like the correct category anymore. For my own future reference, where should I have posted a question like this?

[kuovonne]

Welcome to the Airtable community!

Thank you for posting your detailed explanation of your solution.

David_Needham:

Since I didn’t actually need a scripting app, this doesn’t seem like the correct category anymore.

Don’t worry too much about it. Many people think they need scripting solutions but eventually find a non-scripting solution.

If you aren’t sure what type of solution you need, posting in the “Ask the community” is always a safe bet.