3 Ways To Extract a URL From a Hyperlink in Google Sheets

How do you extract a URL from a hyperlink in Google Sheets? As someone who deals with a lot of spreadsheets, I often find myself dealing with many hyperlinks.

Although opening hyperlinks in Google Sheets is easy, extracting a URL from rich texts may differ. Unfortunately, Google Sheets doesn’t have a native function to get URLs from links.

There are three ways you can extract a URL from a hyperlink in Google Sheets, including:

• Copy and paste from the “Edit link” options from the right-click menu
• Use the Hyperlink Function (=hyperlink)
• Use the Hyperlink Function with “Apps Script“

In this article, I’ll show you how to extract a URL from a hyperlink in Google Sheets. I will also demonstrate how to make a custom function in Google Sheets. Keep reading to learn more.

How To Extract a URL From a Hyperlink in Google Sheets

Let me demonstrate the three methods below to extract a URL from a hyperlink in Google Sheets.

How To Extract Hyperlink From Google Sheet Cell

This method is straightforward to grasp. It requires you to manually extract a URL from a hyperlink in Google Sheets. This method works best when extracting multiple URLs from Google Sheets. Just follow the steps below to get a hyperlink from a cell:

1. Open Google Sheets and the dataset spreadsheet that contains the hyperlink.
2. Click on the cell to select it.
   •  Right-click on it to show the drop-down menu.

3. Click the “Edit link” button in the drop-down menu to open a smaller window under the cell.
   • This window contains the raw text (valid range) and the hyperlink.

4. Click and highlight the URL by clicking and dragging your cursor across it.
5. With the URL selected, press the keyboard shortcut Ctrl + C to extract the copied URL from cell Google Sheets.

6. Close the menu by clicking anywhere else in the spreadsheet or hit the “Apply” button.
7. Click on another cell where you want to paste the URL and use the Ctrl + V keyboard shortcut to paste it.

Use a Preview Instead

Another method to extract a URL in Google spreadsheets is the “Preview” method, which I use regularly.

Hover your cursor over the cell where you want to extract a URL from a hyperlink in Google Sheets. This will show a URL preview in a smaller window beside the cell. Here, click on the “Copy link” icon, which will copy the link to the clipboard.

Want to learn how to extract a URL from a hyperlink in Google Sheets in 2 minutes? Watch this short tutorial now! Alternatively, check out our YouTube channel with other helpful videos on Google Sheets and more!

How To Extract a URL From a Hyperlink in Google Sheets Using the Hyperlink Function

The Hyperlink Function in Google Sheets allows users to create hyperlinks inside a cell. This method to extract the URL of a hyperlinked cell in Google Sheets only works for links created using the function.

To extract a URL from a hyperlink in Google Sheets, I will use the FORMULATEXT and REGEXEXTRACT formulas. Before I show you how to extract the link, let me show you the syntax for the formulas.

The syntax for the REGEXEXTRACT function is:

=REGEXEXTRACT(text, expression)

The formula requires two inputs to work:

• text, which defines the text or the cell address containing the text
• expression, which contains the first part of the text parameter that matches the defined string

The syntax for the FORMULATEXT formula is:

=FORMULATEXT(cell) 

When you insert the formula correctly, it will display the FORMULATEXT within the formula bar when selecting a cell. If the selected cell and formula are correct, the FORMULATEXT will adequately handle this and avoid a circular reference.

To get the URL from a HYPERLINK formula process, I will nest the FORMULATEXT function in the REGEXEXTRACT formula for the Google Sheet.

In this example, I have a cell in my spreadsheet with the following formula:

=HYPERLINK("www.Google.com","Google")

Related: The Easiest Way to Jump to a Cell or Range in Google Sheets

Follow the process below to extract a hyperlink from Google Sheets:

1. Open the spreadsheet with the hyperlink.
2. Left-click your mouse button on an empty cell and add the starting part of the REGEXEXTRACT, which is =REGEXEXTRACT(.
3. Write the text parameter.
   • In this example, I will nest the FORMULATEXT formula. Therefore, I write the starting part of the function, which is FORMULATEXT(.
4. Here, write the cell parameter, which is the cell containing the hyperlink.
   • For this example, it is the cell A1.
5. Add a Closing Bracket “ ) ” to finalize the FORMULATEXT function.
6. Add a Comma ( , )  to separate the parameters and write the expression parameter.
   • For this parameter, write “(www.[^””]+)” including the double quotes (quotation marks) ( “” ), which I will explain the uses about this below.
7. Add a Bracket “ ) ”  and press the “Enter” button to execute the formula.

For the expression parameter in REGEXEXTRACT, using “(www.[^””]+)” means that the function will look for a URL pattern match. I can also write this as “(https?://[^””]+)” and the function will look for https:// in the URL match.

In my experience, using “(www.[^””]+)” is more suitable as most links contain the “www.” prefix.

Related: How to Remove Hyperlinks in Google Sheets

How To Extract a URL From a Hyperlink in Google Sheets Using a Formula From Apps Script

This method to GETURL Google Sheets can be complicated for beginners as it requires you to write custom code using the Google Apps Script.

In this example, Google Sheets doesn’t have a built-in function to extract text from the links, so I will show you how to create a custom function.

Here is how to create a custom GETLINK function using Apps Script:

1. Open any Google Sheets spreadsheet and click the “Extensions” button in the main bar.
   • Click the “Apps Script” button to open a new tab in your browser window.
2. Click on the code editor and delete any code there.
   • Copy and paste the following code into the text editor:

function GETLINK(input){

  var sheet = SpreadsheetApp.getActiveSheet();

  var range = sheet.getRange(input);

  var value = range.getRichTextValue();

  var url = value.getLinkUrl();

  return url;

}

3. With the code pasted into the window, click the “Save” icon in the toolbar.

This will create a custom function called “GETLINK” in Google Sheets, where you can use the URL extractor function. Follow the steps below:

1. Open Google Sheets and go to the spreadsheet where you wish to use the formula.
2. Click on an empty cell and enter the starting part of the custom formula, =GETLINK(.
3. Enter the parameter, which is the cell containing the hyperlink.
   • Write the cell address in Quotation Marks ( “” ) for this custom formula.
4. Add a Bracket “ ) ”  and press the “Enter” button to execute the formula.

An alternative method is to extract URLs from multiple cells using Apps Script.

Related: How to Hyperlink in Google Docs in 2 Seconds

Conclusion

As I did with my guide on how to make a box and whisker chart, in this tutorial, I showed you an innovative way to extract a URL from a hyperlink in Google Sheets.

Although Google Sheets doesn’t have a built-in function that lets me extract copied URLs, there are alternate methods. If they don’t get the job done for you, I have also demonstrated how you can create a custom function using Apps Script in Google Sheets.

I encourage you to try all the methods in this article to see which works best for you.

If you’re looking for templates for Google Sheets, check out some of the ones I made at the SpreadSheet Point store. Use the promo code “SSP” to get 50% off all templates.

Get Premium Templates

Related:

• How to Insert a Google Sheets Hyperlink in 5 Seconds
• 20 Google Sheets Shortcuts You Must Know!