Extract the hyperlink embedded in a word into the cell adjacent using Google Sheets

Question

I copied a list of trade show exhibitors from this website (https://s23.a2zinc.net/clients/corcexpo/pla2024/Public/Exhibitors.aspx?Index=All)and pasted it into a Google Sheet document. Each exhibitor name is a hyperlink to the exhibitor's description page and if I hover on the company name on Sheets, the website appears and I can access it with a click. I am looking for a function that will extract the URL associated with that company name into the adjacent cell. I could tap on the word,

---

click edit link, copy it and paste it manually but I am really hoping there is a less manual way to do it... I would love a formula that I can just drag down and apply to all the other hyperlinks in each cell, returning a list of URLs. Do you know of a Google Sheets function/code that would allow me to accomplish this?

I am not a developer and the easier the solution (little to no code, if possible) the better.

Thanks a million!

Answer 1

First add this function in the container bound AppsScript of your spreadsheet. Then use it as a custom formula in sheets.

function extractUrl(input) {
  var url = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(input).getRichTextValue().getLinkUrl();
  return url;
}

Let's say your hyperlinked value is in cell N2, you can use this custom formula in Google sheets to to get the URL.

=extractUrl("N2")