Extract URLs from HYPERLINKs: REGEXEXTRACT, Apps Script

Introduction

Google Sheets’ HYPERLINK function lets you embed clickable links directly inside a cell, but the underlying URL often stays hidden behind the displayed text. When you need to audit, migrate, or repurpose those links, extracting the raw URLs becomes essential. This article walks you through several reliable methods—ranging from simple formula tricks to custom Apps Script solutions—to pull the actual address out of a HYPERLINK cell. You’ll learn how each technique works, when it’s most appropriate, and how to adapt it for large data sets. By the end, you’ll have a toolbox of options that turn opaque link formulas into clean, reusable URLs ready for analysis or export.

Using REGEXEXTRACT on the Formula Text

The quickest way to retrieve a URL is to treat the cell’s formula as plain text and apply a regular‑expression pattern. The FORMULATEXT function returns the exact string entered, allowing REGEXEXTRACT to isolate the link:

• =REGEXEXTRACT(FORMULATEXT(A2), """(https?://[^""]+)""")
• The pattern looks for http or https followed by any characters until the next quotation mark.
• This method works for single‑link cells and can be dragged down to process entire columns.

Because it relies only on built‑in functions, no script permissions are required, making it ideal for quick, on‑the‑fly extractions.

Leveraging the HYPERLINK Function Itself

If the cell contains a HYPERLINK formula with a static URL (e.g., =HYPERLINK("https://example.com","Click")), you can extract the address by splitting the formula string:

• =INDEX(SPLIT(FORMULATEXT(B3),"""),2)
• SPLIT breaks the text at each double‑quote, and INDEX selects the second element, which is the URL.
• This approach avoids regular expressions and is easier to read for users unfamiliar with regex syntax.

It works best when the formula follows the standard two‑argument structure; variations (like optional third arguments) may need slight adjustments.

Creating a Custom Apps Script Function

For more complex scenarios—such as extracting URLs from cells that contain multiple hyperlinks, or when you need to batch‑process thousands of rows—a custom Google Apps Script offers flexibility. Below is a simple script you can add to your spreadsheet:

• function EXTRACTURL(cell) {
•   var formula = SpreadsheetApp.getActiveRange().getFormula();
•   var match = formula.match(/"(https?:\/\/[^"]+)"/);
•   return match ? match[1] : "";
• }

After saving, use =EXTRACTURL(A2) like any native function. The script parses the formula with a regular expression, returns the first URL it finds, and can be expanded to loop through ranges or handle error cases.

Batch Extraction with ARRAYFORMULA

When you need to pull URLs from an entire column without writing a formula in each row, combine ARRAYFORMULA with the techniques above. For example, to extract URLs from column C starting at C2:

• =ARRAYFORMULA(IF(LEN(C2:C)=0, "", REGEXEXTRACT(FORMULATEXT(C2:C), """(https?://[^""]+)""")))
• This single formula populates a parallel column with all extracted links, automatically ignoring empty cells.
• Pair it with FILTER or UNIQUE to generate a clean list of distinct URLs for reporting.

Using ARRAYFORMULA dramatically reduces manual effort and keeps your sheet dynamic—any new HYPERLINK entries are instantly processed.

Conclusion

Extracting the hidden URLs from Google Sheets’ HYPERLINK function is far from a mystery once you understand the underlying tools. Simple formula tricks like REGEXEXTRACT combined with FORMULATEXT offer a fast, no‑code solution for most single‑cell needs. For predictable formulas, splitting the text provides a clear alternative, while custom Apps Script functions unlock power for complex or large‑scale extractions. Finally, wrapping these methods in ARRAYFORMULA lets you handle entire columns effortlessly. By selecting the approach that matches your workflow, you can transform opaque link formulas into actionable data, streamlining audits, migrations, and any analysis that depends on clean URL lists.