I’ve copied some hyperlinks from a website and pasted them into my Google Sheets. They appear as clickable text but are not HYPERLINK formulas, even though they work fine. My goal is to extract the actual URL from each hyperlink and place it in the adjacent column using a formula.
For instance, I want it to look like this:
=GETURL(A1)
This should give me just the URL without any of the display text. I’ve seen methods that work with HYPERLINK formulas, but those don’t apply to my situation since I’m using pasted links. It seems like the URL is stored somewhere, but I don’t know how to access it. I would prefer a formula-based solution, but I’m open to Google Apps Script if necessary. Has anyone encountered this problem before?
Nope, Google Sheets doesn’t have a formula for extracting URLs from pasted hyperlinks. When you paste links directly into cells, they become rich text objects instead of formula-based hyperlinks. Sheets can’t access that URL data with standard formulas.
I hit this same problem last year importing data from websites. The URLs are stored in the cell’s metadata, but you can’t reach them with formulas like GETURL.
Your best option is Google Apps Script. Write a custom function that accesses the cell’s rich text formatting and pulls out the link URL. It’s pretty straightforward once you learn the RichTextValue methods, but you’ll need to ditch the pure formula approach.
Had this same problem scraping web data. Pasted hyperlinks are just rich text formatting - Google Sheets can’t grab the actual URLs as formula data. It’s display formatting stuck to the cell, not something you can extract normally. I tried a bunch of workarounds but ended up writing a simple Apps Script that loops through the RichTextValue object and pulls out the URLs. Takes about 10 minutes if you know basic scripting. You could paste as plain text with Paste Special, but then you lose the clickable links. Apps Script’s really your only option if you want to keep the original links AND extract the URLs.
totally feel ya! yeah, Sheets just doesn’t have a built-in workaround for this. when those links paste as rich text, it’s a pain to extract the URLs using formulas. had better luck using Apps Script with getRichTextValue() and getLinkUrl() to pull em out.