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?
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 href
s 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.