How to Extract Link from a Column?

Hey everyone,

This has been bugging me for quite some time.

  1. The link auto format feature is a bit annoying especially if I just want to see the actual LINK text instead of the title of the page it links to (in our case, it links to JIRA which requires a login so ALL the links that we have have the same title).
  2. Say for example a text has a predictable format up to a certain point (e.g. a hyperlink for JIRA tickets), and I want to get the ticket code from the link, after trying so many things, I cannot seem to find the proper way to do this. IS there a way to do this??? :open_mouth:

At the very least have a way to be able to get the “LINK” from the pasted link in the column. I understand how some people might want to title their links differently when in Coda, but it’s a bit too tedious to do that one by one when you’re pasting lists of links into a doc.
image

For your first question, I did the following:

Right click the link, select Edit, and delete everything from the Text line in your picture above. This caused it to default to the link text. This may not be ideal, especially for large sets of links, but it was an immediate workaround for me.

Not sure if you are looking to do this, but if you want to remove the hyperlink and convert to text, I put the following formula in a second column:

hyperlink

To your second question, this is a very hacky method, and again, others may have a better idea:

I assumed the “EQPH-18” was the ticket code you want to extract for this question. If the hyperlink will always be the same before the ticket code portion, then you will know where the code starts (in this case, at character number 40). The 60 is to account for however long the code may be (i.e. if the ticket code is 20 characters, it ensures all of it will be captured). You can also try using the Right function (https://coda.io/formulas#Right), but only if the hyperlinks are always formatted exactly the same way.

Hopefully this helps at least a bit.

Thank you for this. Technically the removing of the “Text” part of the Link was enough to remedy the situation. But I was hoping there was a way to do this in a formulaic way. Maybe to even name the link as the ticket code (and you were correct, I wanted to extract the “EQPH-18” part) but it looks like there really is no way to do it on Coda yet.

To get the hyperlink separate from the “Log in with Atlassian Account” title, you can use this in a new column:

Link.Embed().ToText()

You’ll see in the screenshot that I have full links and the link with the title, and this extracts the actual link text from both.

Then you can create a hyperlink with the ticket number as the title:

If([Link Text].IsNotBlank(),thisRow.[Link Text].Hyperlink(Right(thisRow.[Link Text],[Link Text].Length() - 39)),"")

I used an “If” statement so there isn’t an error if there is ever a row where there is no link, but if every row has a link, you can remove the “If”.

You can probably boil this down even more to get a full formula in only one extra column, but breaking it up in two helped me visualize each step a little better.

Here’s the same solution that works in just one extra column:

Link.Embed().ToText().Hyperlink(Right(Link.Embed().ToText(),Link.Embed().ToText().Length() - 39))

This removes the “If” statement as well, so it looks a little cleaner if you have a link in every row.

Ohhh! This really helped! So there was a function for hyperlinks haha thanks!

Glad it worked! :+1: