How to extract url from Google Sheet Cell with anchor text hyperlink

Question

I have copied a list of links from the web to a google spreadsheet.

Now there is a list of hyperlinked text in each cell.

I want to put the url underneath the anchor text into a new cell.

However, the only methods discussed online refer to how to get a url from a cell that is using the HYPERLINK() function. My cells are not using such a function, they are simply copied and pasted from a website, but still retain their anchor text and hyperlink. Other options use Macros/VBA, which I cannot use/have access to.

How can I replace these anchor text in a Google Sheets cell with the links they represent or copy them into a new cell?

Answer 1

This only a partial answer, but I spent an embarrassing amount of time recently trying to extract links from a spreadsheet that were done the way you describe.

The only way I was able to finally do it was to export the sheet as HTML, and use regex to find the hrefs and extract them.

Full discloser, my wife came up with this in about 10 minutes, meanwhile I wasted many hours playing with the Google Sheets API, Open Office, Excel, and various xlsx parsing libraries for Go.

Anyway - if you just need to extract the links, it's not impossible, but it's not very practical either.

Answer 2

I used =formulatext to change the hyperlinked text back to text and then =REGEXEXTRACT([your range],"http[s]?://(?:[a-zA-Z]|[0-9]|[$-_@.&+]|[!*\(\),]|(?:%[0-9a-fA-F][0-9a-fA-F]))+") to extract the URL itself.