Help

Replace urls in text string

Topic Labels: Formulas
3251 10
cancel
Showing results for 
Search instead for 
Did you mean: 
rmm
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello,

New to airTable and I love it. In setting up a database, I am running into a problem. Links from a long text string have to be replaced with new links. Is there a formula that an do this or does this have to be done manually?

Here’s the text: <h4>UNIT OVERVIEW</h4><p><a href="https://apostolicfaithchurch.box.com/s/q77dw02m7v5mu11fg301ixmtbni56ebb">Men Who Trusted God</a><br></p><h4>MEMORY VERSE IN ACTION</h4><p>WHOSO — Point to others.</p><p>PUTTETH HIS TRUST — At eye-level, hold fists on top of each other as though holding to an imaginary rope.</p><p>IN THE LORD — Make an L with thumb and first finger of the right hand. Touch thumb to left shoulder, bring across in front of body touching right waist.</p><p>SHALL BE SAFE — wipe sweat from forehead as though just having a close call with danger.<br></p><h4>MEMORY VERSE VISUALIZED</h4><p>Though our memory verse for this week is found in Proverbs, it ties in closely with our story text about Noah and the ark. The visualization for this verse is the ark. (See pattern at the end of this lesson.) Cut out each piece and assemble in the correct order as your students learn the verse.</p><h4>MEMORY VERSE SONG</h4><ul><li><a href="https://apostolicfaithchurch.box.com/s/9gynfedpkg86inoiztrd7qjzop981ef9">Trust In The Lord</a></li></ul><h4>COORDINATING SONGS</h4><ul><li><strong>"Brother Noah" </strong>— tune of "Old McDonald"</li></ul><h4>MEMORY VERSE PATTERN</h4><ul><li><a href="https://apostolicfaithchurch.box.com/s/vc5pj6c1jp77hcj9bbqkxj40thpvyko0">Proverbs 29:25</a><br></li></ul><h4>PATTERNS AND ACTIVITIES</h4><ul><li><a href="https://apostolicfaithchurch.box.com/s/5pemga35jz6sk7ls4g17kwqu9pprt9ci">Unit 24 Patterns</a>‍</li></ul><h4>SUPPORT MATERIAL</h4>

And these are the new links:

ACTIVITY

OVERVIEW

MEMORY VERSE SONG

PATTERNS AND ACTIVITIES

MEMORY VERSE PATTERNS

Can this be done with a formula? Do the PDFs need to be brought into AirTable?

Thank you in advance.

10 Replies 10

I am not certain this could be done using one or more formulas. Is this something that must be done for every or several records in a table? Is there any kind of consistency to how many links are in the text field, and what those links need to be replaced with?

rmm
5 - Automation Enthusiast
5 - Automation Enthusiast

@Kamille_Parks Yes, each table has approximately 200+ records. Each record will have the same amount of links that have to be changed. And those url will always be after those headings.

It helps that there’s some level of consistency, but in my first few attempts at a formula-based solution I wasn’t able to reliably extract out the urls then replace them with the ones you need.

rmm
5 - Automation Enthusiast
5 - Automation Enthusiast

@Kamille_Parks Thank you for trying! I really appreciate it.

Welcome to the community, @rmm!

You can very easily do this with Integromat, which is a low-code/no-code automation & integration platform that has full Airtable support.

Integromat has a text parsing module that lets you extract an unlimited number of links from HTML code, and it will return them all to you as separate elements to do whatever you’d like to do with them:

rmm
5 - Automation Enthusiast
5 - Automation Enthusiast

@ScottWorld Thanks Scott. I tried setting it up without success. A true no-coder here. Any tips on how to set it up?

Welcome to the Airtable community!

Do you have a way of automatically knowing what the new link should be from the old link? Do you have a table that maps the old links to the new links?

Is this an ongoing conversion process or a one-time process?

How do you feel about having a script to perform the conversion? Do you have budget to hire someone to write a script?

rmm
5 - Automation Enthusiast
5 - Automation Enthusiast

@kuovonne Thank you! We could create columns for each in that same table and bring them into the appropriate row, though that in itself is quite a bit of work. Currently, those files are saved in Oracle and would require going into each record and copying its link.

This is a one-time process. I would be interested in a script. What would be the setup needed for it to work?

A script can search for a long text field for urls and replace them with other urls. However, the script needs to know what the new urls should be. For example, you can provide a list of urls for the script to replace in matching order. Or you can provide a table of matching urls and the script will lookup the new url from the table based on the original url. It really depends on the current structure of your data.