How to Extract URLs from Hyperlinks in Google Sheets

Показать описание

In this Google Sheets tutorial I'll be showing you two main types of hyperlinks in Google Sheets and how to extract links from both types. This includes creating and using a custom function with Google Apps Script to extract URLs, even when you have multiple hyperlinks within a single cell.

In this video, we'll first learn how to create hyperlinks using the HYPERLINK formula and directly within a cell (Ctrl+K). Then, I will guide you through the nuances of these two methods and demonstrate how to extract URL data from both using the FORMULATEXT function and a custom script.

Make sure to let me know in the comments if this video has been helpful and give it a like. Your feedback helps me improve and deliver more useful content.

----------------------------------------------------------------------------------------------------
⏳ TIMESTAMPS
----------------------------------------------------------------------------------------------------
0:00 The two types of links in Google Sheets
0:31 Extracting links from the HYPERLINK function
1:43 Original script I found online (link in description)
3:03 Problem #1: Case Sensitivity
3:43 Fixing the first problem
5:15 Problem #2: Multiple links
6:11 Fixing the second problem
7:40 How to transpose in Google Apps Script
8:07 Final result

----------------------------------------------------------------------------------------------------

💬 GOT A QUESTION?
↓↓↓ Leave a comment down below ↓↓↓

Рекомендации по теме

Комментарии

Awesome, exactly what I needed and well explained, thanks

SCZiful

This is really nice and detailed. It's unfortunate extracting the RichText source isn't a built-in function.

douglasheld

This was actually soooo helpful thank you so much

BrysonKeyes-tc

This video greatly helped sir, saved a ton of time. I don't understand anything in these scripts, just copied your code and it magically works. THANK YOU

enyeone

Thanks, works great. I don't understand why google doesn't have a built-in function for this.

BigBoyAdvance

Hello thanks for sharing the script, but I'm getting error if the hyperlink is in the same google drive for example image link uploaded in the same google drive. Anything I can do to fix this?

#ERROR!
TypeError: Cannot read properties of null (reading 'getRuns') (line 6).

bonnylisking

Great videos thank you, has helped me a lot (I'm not very savvy in this). I'm working on a project and I'm trying to extract a hyperlink from 1 column, column C, and put it into another column, D but within Parenthesis of column D. Is there a formula for that?

Elisummit

Absolutely helpful from start to end !!

sam_oml

it was incredibly helpful!! you are the best! thanks!!

Sonehan

Very very good. I have to make a python crawler and now i can finish my project in case of your tutorial. Many thanks - it saved me lot of time.

mister_maxwell

I don't know what I'm doing wrong, i don't see a list of sheets to choose from when I'm trying to create a link (internal link) kinda like a table of contents that I'm trying to do. Any help would be greatly appreciated.

myofficeonline

Thank you! I've been trying to do this for ages but all the other scripts I found didn't work.

Just wondering if there is a way to use a variation of this script in google docs. So the script runs through a document and then exports the links URL and anchor text to a spreadsheet

Bagsalwayspacked

it works on my google sheet, tqvm for the awesome script 🙏 In a multiple hyperlink cell, is there a way to extract only one single URL hyperlinked to a specific word, say for example 'view' ?

jeffreytang

I wanted to give it a thumbs up, but that would change it 405 from 404. I love your work but 404 is fun.

muddasserawan

Hello, I'm attempting to find an easy way to directly link cell to cell with a hyper link. For instance, I want cell H11 to link me to nots in HH11, and HH12 to Hyper link me "back" to H11. I want to expand this to all my cells in an easy way. I want this same hyper link function, because it can be interchangeable with other sheets. The issue is easily getting the values to expand, instead of changing each link. The hyperlinks to google don't reveal a pattern that is practical to change either. I just want to link to the same line, H11 to HH11, and back, all the way down the document to H1000, etc, to HH1000 etc. Can you help me out?

danielsneighborhood