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?

Now you can get ancor link from cell by custom function from this answer webapps.stackexchange.com/a/89564/86039 I test it and it works. — vatavale
– vatavale, Commented Aug 20, 2017 at 20:20
Possible duplicate of How to extract URL from Link in Google Sheets using a formula? — Ryan Tarpine
– Ryan Tarpine, Commented Mar 22, 2018 at 6:51

evillemez · Accepted Answer · 2017-02-17 03:27:35Z

5

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.

answered Feb 17, 2017 at 3:27

evillemez

662 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Corey B Over a year ago

Once you export as HTML, you can open the file in a browser, and then use Javascript (in the console) to get the urls out. @evillemez , you may want to include a js snippet in your answer that extracts the links, like:: let links = document.querySelectorAll('a'); for (let link of links) { link.innerHTML = link.href; }

Jake Peralta · Accepted Answer · 2024-07-08 08:36:58Z

1

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.

edited Jul 8, 2024 at 8:36

Jake Peralta

5473 silver badges15 bronze badges

answered Jul 5, 2024 at 15:52

Costijn

111 bronze badge

1 Comment

Yunnosch Over a year ago

Welcome to StackOverflow. Please, edit and try for How to Answer, describe the effect of what you propose and explain why it helps to solve the problem. Find help with formatting your post here: stackoverflow.com/help/formatting . Consider taking the tour.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related