Help

Re: Converting html to url

1352 0
cancel
Showing results for 
Search instead for 
Did you mean: 
MissSteph
4 - Data Explorer
4 - Data Explorer

Hello, I have a whole column of data with html links and I need these to be clickable links with the respective titles: 

DATA: 

<a href=https://www.coronavirus.tas.gov.au/media-releases/important-coronavirus-test-information>Tasmanian Government: Important coronavirus test information</a>

 

Needs to be 

Tasmanian Government: Important coronavirus test information

How can I achieve this via airtable? 

There are so many, relinking them all by hand is going to be tedious. Thanks

I will ultimately be exporting this to excel.

6 Replies 6
Marko_K
5 - Automation Enthusiast
5 - Automation Enthusiast

You can extract the URL or title, but I don’t think you can’t embed a URL within the title like you did in your post, at least not in a URL field. Both URL and titles have to be in their respective fields. There are a few ways to do it, the easiest being a script, but if you want to just use formula fields, I think the MID function can help you: 

[Mid] function lets you extract a certain number of characters starting at a specific place within a string. It follows this format: MID(string, whereToStart, count).

The variables are:

string: Your anchor tag

whereToStart: Right after the “=“ symbol

count: Distance between whereToStart and where the URL ends (right before the “>” symbol)

So, you will need to figure out the starting position and count (distance from start to end):

  1. Starting position right after the “=“:  FIND("=", {Anchor Tag}) + 1
  2. Ending position right before the “>“:  FIND(">", {Anchor Tag})
  3. To get the count, you need the distance between (2) & (1), so subtract (2) - (1):  FIND(">", {Anchor Tag}) - (FIND("=", {Anchor Tag}) + 1)

Putting it all together:

MID({Anchor Tag}, FIND("=", {Anchor Tag}) + 1, FIND(">", {Anchor Tag}) - (FIND("=", {Anchor Tag}) + 1))

Here’s a link to a base with everything put together. I’ll leave it up for a few days:

Zemanir Airtable Consulting: www.zemanir.com

How would you be exporting this to Excel and what version of Excel are you using?

If you're exporting your Airtable data as a CSV I vaguely recall that there's a `=HYPERLINK()` formula for Excel, so you might want to format your Airtable data into that format first before the export

KLCH
5 - Automation Enthusiast
5 - Automation Enthusiast

Can someone tell me the "simple" script for a formula field which would either:

1) Add a defined URL, add FIELD1 data behind it, and display FIELD2 as the text for the defined formula field.

2) Display the text from FIELD2 whilst linking to the URL defined in FIELD1

Thank you. 

 

You can't use a formula field for this I'm afraid.  You'll need an automation that updates a long text field with rich text formatting turned on

Screenshot 2024-02-18 at 1.31.14 PM.png

Screenshot 2024-02-18 at 1.31.10 PM.png


You could also do this with JavaScript via the script extension or Run a Script automation action

Link to base

KLCH
5 - Automation Enthusiast
5 - Automation Enthusiast

Thanks. I am sure that this would be very helpful but regretfully I can't see anyone's screenshots. They all appear as broken links. 

Edit: cleared my cookies and now it works

Thanks

KLCH
5 - Automation Enthusiast
5 - Automation Enthusiast

This isn't so straight forward from Zapier. Please see:

Zapier process:

KLCH_0-1708343718933.png

Airtable results:

KLCH_1-1708343725072.png

I found the final solution here which turned out to be:

Zapier process:

KLCH_2-1708344555564.png

Result:

KLCH_3-1708344586164.png