How to Extract URLs from HYPERLINK Function in Google Sheets

The HYPERLINK function in Google Sheets is a convenient way to link text to URLs within a spreadsheet. However, there may be times when you need to extract the URL from a cell that contains a hyperlink. Whether you're working with a large dataset or automating a task, extracting the URL can save time. In this guide, we’ll show you how to extract URLs from the HYPERLINK function in Google Sheets.

Why Extract URLs from the HYPERLINK Function?

When using the HYPERLINK function in Google Sheets, the URL is embedded within the text of the cell. However, in some cases, you may want to isolate the URL itself for further analysis, reporting, or automation. Extracting URLs can be useful for:

• Data analysis, such as gathering a list of all URLs in a sheet.
• Exporting URLs for use in external tools or systems.
• Creating dynamic reports that include extracted URLs.

What You Need:

• Google Sheets Account: You need a Google Sheets document with hyperlinks to extract URLs from.
• Basic Understanding of Google Sheets Functions: Familiarity with basic functions like HYPERLINK and text functions.

Steps to Extract URLs from the HYPERLINK Function in Google Sheets

Step 1: Understand the Structure of the HYPERLINK Function

The HYPERLINK function in Google Sheets is used to create clickable links. The syntax looks like this:

In this example, the URL https://www.example.com is linked to the text "Click Here". While the text is visible in the cell, the URL is embedded behind the scenes. To extract the URL, you will need to reference the cell containing the hyperlink.

Step 2: Use the REGEXEXTRACT Function

To extract the URL from a cell that contains a hyperlink, you can use the REGEXEXTRACT function. This function allows you to search for specific patterns in a string, such as a URL.

This formula will search for a URL in cell A1 and extract the first URL it finds. The regular expression "https?://[^\s]+" matches URLs that start with http:// or https:// followed by any characters that are not spaces.

Step 3: Apply the Formula to Multiple Cells

If you have a column of cells that contain hyperlinks, you can apply the REGEXEXTRACT function to an entire range using ArrayFormula:

This formula will extract the URLs from all cells in the range A2:A and display them in the corresponding rows. The ArrayFormula ensures that the formula is applied to the entire column.

Step 4: Clean Up the Data (Optional)

If you need to further clean up the extracted URLs (for example, removing unwanted characters or trimming spaces), you can use additional functions like TRIM or SUBSTITUTE:

This formula will remove any leading or trailing spaces from the extracted URLs.

Examples of Extracting URLs

Example 1: Extracting a Single URL

If cell A2 contains the following HYPERLINK function:

You can extract the URL using the following formula in cell B2:

This will return https://www.example.com in cell B2.

Example 2: Extracting URLs from a List of Hyperlinks

If you have a column of hyperlinks and want to extract all URLs into a separate column, you can apply the ArrayFormula with REGEXEXTRACT to the entire range:

This formula will extract the URLs from all cells in column A and display them in the corresponding cells in column B.

Troubleshooting Tips

• No URL Extracted: Ensure that the hyperlink in the cell is properly formatted. The REGEXEXTRACT function relies on the correct structure of the URL.
• Empty Results: If the result is blank, check if the cell contains a valid URL or if the formula is applied correctly.
• Handling Different URL Formats: If your URLs contain special characters or complex parameters, modify the regular expression to account for different patterns.

Conclusion

Extracting URLs from the HYPERLINK function in Google Sheets is easy and can be automated using the REGEXEXTRACT function. This method works for a single cell or a whole column, allowing you to dynamically collect URLs without manually copying them. Whether you're organizing links or building reports, these techniques can save time and streamline your work.