How to Extract URL from Hyperlink in Google Sheets (3 Ways)

The data we use in our worksheets often contain hyperlinks to other pages or files. We can also use several methods like the HYPERLINK function, or the Insert Link command to create hyperlinks. Now, there might be various reasons behind one trying to extract URL from a hyperlink in Google Sheets. For example, one might simply want to use the URL in another sheet or file. However, here I’ll discuss 3 easy ways to extract URL from any hyperlink in Google Sheets.

A Sample of Practice Spreadsheet

You can copy our practice spreadsheets by clicking on the following link. The spreadsheet contains an overview of the datasheet and an outline of the described ways to extract URL from any hyperlink in Google Sheets.

3 Easy Ways to Extract URL from Hyperlink in Google Sheets

First, let’s get familiar with our dataset. The dataset contains a list of several Oscar-winning films over the years. As you can see, the name of the films is hyperlinked to their Wikipedia pages using the dynamic HYPERLINK function. We’ll extract the URLs from these hyperlinks.

1. Applying Manual Method

The simplest way to extract the URL from a hyperlink is to manually copy and paste the URL. Although this method is very simple, it is not very efficient to manually extract URLs in large datasets.

Steps:

• For this method, we first have to copy the URL from the hyperlinked cell. There are several methods of copying the URL from a hyperlink.
• For example, you can simply hover your mouse pointer over the required hyperlinked cell. The hyperlink will be visible and from there click on the Copy Link icon.

• Another way to copy the data is to utilize the Edit Link command.
• Select the required hyperlinked cell (Cell C5) and then click on the right button of your mouse.
• From the pop-up commands, select the Edit Link You can also find the Edit Link command in the Insert ribbon commands.

• After clicking on the Edit Link command, a window like the following will pop up.
• Now, click on the link and select it by using the keyboard shortcut CTRL+A and then copy the link using the keyboard shortcut CTRL+C.

• Next, select the cell where you want to paste the URL (Cell D5).
• Finally, use the keyboard shortcut CTRL+V to paste the copied URL.

• Now, continue the previous steps for other cells too.

Read More: How to Get Hyperlink from Cell in Google Sheets (4 Quick Tricks)

2. Combining REGEXEXTRACT and FORMULATEXT Functions

Since manually extracting the URLs from hyperlinks is not very efficient for large datasets, you can apply a simple formula. The formula is formed by combining the REGEXEXTRACT and FORMULATEXT functions. However, you must remember that this method only works if the hyperlink is created using a formula.

Steps:

• First, select Cell D5.
• Afterward, type in the following formula-

• Finally, press Enter key to extract the URL from the hyperlink in Cell C5.

• Now, hover your mouse pointer above the bottom-right corner of Cell D5 and use the Fill Handle icon to copy the formula to other cells.

• Thus, you can extract URLs from hyperlinks for a large dataset in Google Sheets.

Read More: How to Hyperlink Data to Another Sheet with Formula in Google Sheets

3. Using Apps Script

Another way to extract URLs from a large data set is to write a simple script in Apps Script. Here, we’ll use the Apps Script to create a custom menu in Google Sheets which will extract URLs from hyperlinks.

Steps:

• First, go to the Extension ribbon and select Apps Script from there.

• This will open a new window like the following in your browser.

• Rename the project and delete the default code.
• Afterward, enter the following script-

function extract_url_from_hyperlink() {
const ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom_Menu').
  addItem('Extract the  URL from Hyperlink','extract_url').
  addToUi();
}

function extract_url() {
  let ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let active_range = ss.getActiveRange().activate();
  let range_a1notation = active_range.getA1Notation();
  const data_range = ss.getRange(range_a1notation);

  const url_range = data_range.getRichTextValues().
  map(row=>row.map(value=>
    value.getLinkUrl()
  ));
  let urls = url_range.flat();

  for (let i =0; i<urls.length;i++){
    let url = urls[i];
    let req_col = active_range.getColumn()+1;
    let req_row = active_range.getRow();
    ss.getRange(i+req_row,req_col).setValue(url);
  }
}

• As soon as you Save the project, the required function will be ready to run.
• Now Run the project.

• Now, if you return to your worksheet, you’ll notice a custom menu titled Custom_Menu has been created.

• Finally, select the range C5:C11 and go to the Custom_Menu ribbon, and select Extract the URL from Hyperlink option.

• Thus we can extract URLs from hyperlinks using Apps Script in Google Sheets.

Read More: How to Rename a Hyperlink in Google Sheets (2 Quick Ways)

Things to Be Considered

• The method of combining the REGEXEXTRACT and FORMULATEXT functions to extract URLs works only if the hyperlinks are created using any formula.
• Provide required permissions to run the script while using Apps Script.

Conclusion

This concludes our article to learn how to extract URL from hyperlink in Google Sheets. I hope the article was sufficient for your requirements. Feel free to leave your thoughts on the article in the comment box. Visit our website OfficeWheel.com for more useful videos.

Related Articles

• How to Hyperlink an Image in Google Sheets (3 Suitable Ways)
• How to Remove Hyperlink in Google Sheets (5 Simple Ways)