I’m struggling with a Google Sheets problem. I copied some links from a website and pasted them into my spreadsheet. The thing is, they show up as linked text, not as regular hyperlink formulas. They still work when clicked, but I can’t figure out how to get just the URL.
What I want to do is grab only the web address from each cell and put it in the column next to it. Is there a way to do this without using complicated scripts? I’ve been hunting for a simple formula that could do the job, but no luck so far.
Here’s what I’m picturing:
=MAGIC_FORMULA(A1)
Where A1 has the linked text, and the formula spits out just the URL.
I’ve looked at other solutions online, but they all seem to be for different situations. Any ideas on how to crack this? I’d really prefer a formula if possible, but I’m open to other methods if that’s the only way. Thanks for any help!
I’ve actually had to deal with this exact issue in my work recently. While there’s no built-in formula for it, I found a workaround that might help you out. It involves using the REGEXEXTRACT function combined with SUBSTITUTE. Here’s what I did:
This formula first converts the cell content to text, replaces double quotes with single quotes (to avoid formula issues), and then uses regex to pull out the URL. It’s not the prettiest solution, but it worked like a charm for me without needing any scripts or add-ons.
Just pop that into the cell next to your linked text, and it should extract the URL. It might look intimidating at first, but once you set it up, you can just drag it down for all your links. Hope this helps!
hey there! i’ve dealt with this before. sadly, there’s no easy formula for it but don’t worry! you can use the IMPORTXML function to grab the URLs. it’s a bit tricky to set up, but once you do, it works like magic. lemme know if u want more details on how to use it!
I’ve encountered this issue before. Unfortunately, there’s no straightforward formula to extract URLs from linked text in Google Sheets. However, you can achieve this using Apps Script. It’s not as daunting as it sounds.
Create a custom function in the Script Editor (Tools > Script editor) with this code:
function extractURL(input) {
var url = input.match(/(?<=href=“).+?(?=”)/);
return url ? url[0] : ‘No URL found’;
}
Then use it in your sheet like =extractURL(A1). This should extract the URL from the linked text. It’s a bit more involved than a simple formula, but it’s quite effective for this specific task.
but don’t worry! you can use the IMPORTXML function to grab the URLs. it’s a bit tricky to set up, but once you do, it works like magic. lemme know if u want more details on how to use it!