Extracting Long Text Field of URLS, into New Table

[Nate_Hall]

1. I have a “Long Text” Field that contains a list of URLS.
2. There is an associated keyword in the adjacent cell, which is what yielded those URLs listed in the LongText field

keyword | LongTextUrlsField
shoes | [50+ Shoe Related Urls]

Is there a way to extract each of those urls and put them into a new table, along with the corresponding keyword (multiple times) like this?

keyword | longtextUrls
shoes | Url 1 of 50
shoes | Url 2 of 50
shoes | Url 3 of 50…

I have looked at everything from python scripting, to regex formulas, and even AirScript Ideas. Although there are some similar topics out there, nothing did this. Any help would be greatly appreciated!

[ScottWorld]

Welcome to the community, @Nate_Hall!

As far as I know, you would need to write your own custom Javascript script to handle something like that.

Alternatively, it is highly likely that you could do this in a no-code/low-code fashion (i.e. no scripting required) by using Integromat (now known as Make.com). I don’t know exactly which tool of theirs would let you do this, but probably something along the lines of their Iterator tool. You’re essentially looking for a tool that would enable you to loop through all of the URLs, and then create new records for each one. You would probably need to reach out to their tech support team to ask them which tool would handle this for you, but their tech support is slow and it could take 4-5 business days for their tech support to get back to you. If you do reach out to them, please post back here with what they say!

[Kamille_Parks]

You could do this with a few formulas and an Automation.

Assuming your {List of URLs} field follows keyword + | + comma separated list of urls, then you can extract the keyword with this formula:

TRIM(
  MID({List of Urls}, 1, FIND(" |", {List of Urls}))
)

Then use another formula field to augment the original list to put the keyword in front of each URL:

SUBSTITUTE({List of Urls}, ",", "," & {Keyword Extracted} & " | ")

Then use an Automation to copy the {Comma Separated} field into your link to your Keywords table whenever the record gets updated.

Your other table will look like this:

[Nate_Hall]

Thank you for that idea. I have been messing with make.com lately, so I will approach it from that angle. In terms of a Javascript solution, is that something that could be integrated into the Scripts add-on within airtables? I think its called Airscript? If so I might look at that first, just to keep everything under one hood… but the Integromat might just have to work.

[Nate_Hall]

I should have been more clear with my layout/explanation. I have a field that is “Long Text”, that has URLs with line breaks (\n) that I was trying to parse into a new table with the keyword written every time for every url that was in the adjacent “Long Text” cell. So if there were 35 urls, the same keyword would repeated 35 times, and so on for each keyword. So a list of 10 keywords, would yield 350 new rows or records on a new table. I hope that makes sense. Thanks for the help, Ill take a look and see if I can leverage this method.

[Kamille_Parks]

@Nate_Hall

I should have been more clear with my layout/explanation. I have a field that is “Long Text”, that has URLs with line breaks (\n) that I was trying to parse into a new table with the keyword written every time for every url that was in the adjacent “Long Text” cell.

That’s even easier.

The revised formula for {Comma Separated} would be:

Keyword & " | " & SUBSTITUTE({List of Urls}, "\n", "," & Keyword & " | ")

Everything else is the same.

[Kamille_Parks]

Its actually even easier than that if you want to avoid having two rows for “google.com” if its a keyword for both shoes and shirts:

If the {Comma Separated} formula is just:

SUBSTITUTE({List of Urls}, "\n", ",")

Then the second table would look like: