Extract hyperlinks from Excel and Google Sheets (VBA, Apps Script, Python)

To extract hyperlinks from Excel, use a small VBA function or Office Script to read the cell's hyperlink address. To extract hyperlinks from Google Sheets, use Apps Script because the visible cell value and the underlying link are stored separately.

An Excel cell can show "Click here" while linking to https://example.com/long-url. There's no built-in formula in classic Excel that returns the URL behind a hyperlink — you have to dig for it.

Excel 365: HYPERLINK function works one direction only

=HYPERLINK("https://example.com", "Click here") creates a hyperlink. There's no inverse function that reads the URL back from an existing cell. This is the most common surprise.

Excel: tiny VBA function (most reliable)

Open the VBA editor (Alt+F11), Insert → Module, paste:

Function GetURL(c As Range) As String On Error Resume Next GetURL = c.Hyperlinks(1).Address End Function

Save the workbook as .xlsm, then use =GetURL(A1) anywhere in the sheet. Returns the underlying URL or empty if the cell isn't a hyperlink.

Excel: no-VBA workaround

If macros aren't allowed: select the column with hyperlinks, copy, paste into an empty Word document, then copy from Word back to Excel. Word converts hyperlinked text into the visible URL form. Awkward but it works in locked-down environments.

Excel: hyperlinks added via Insert → Hyperlink vs. typed URLs

If users typed the URL directly and Excel auto-linked it, the visible text already is the URL — no extraction needed. The VBA function above handles both cases. The "hidden URL" problem only arises when someone used Insert → Hyperlink (or imported from a source that did).

Google Sheets: built-in friendlier

Sheets doesn't have an inverse HYPERLINK either, but the workaround is cleaner via Apps Script:

function GETURL(input) { const range = SpreadsheetApp.getActiveSheet().getRange(input); return range.getRichTextValue().getLinkUrl(); }

Then =GETURL("A1") returns the URL behind the hyperlink in A1. Note: input must be quoted as a string (the cell reference, not the value) — Apps Script doesn't get the cell reference natively.

Bulk: dump the .xlsx as XML

An .xlsx file is a ZIP archive. Inside, xl/worksheets/sheet1.xml lists every hyperlink with its target. Useful when you have hundreds of cells and want them all in one shot:

unzip -p workbook.xlsx 'xl/worksheets/sheet1.xml' | grep -oE 'r:id="[^"]+"|display="[^"]+"'

For a cleaner programmatic version, openpyxl's Worksheet.cell(row, col).hyperlink.target gives you the URL directly.

Excel 365: Office Scripts (no desktop VBA)

If your org blocks macros but you're on Excel for the web, Office Scripts can read hyperlinks without a .xlsm file. Automate tab → New Script:

function main(workbook: ExcelScript.Workbook) { const sheet = workbook.getActiveWorksheet(); const used = sheet.getUsedRange(); if (!used) return; const vals: string[][] = []; for (let r = 0; r < used.getRowCount(); r++) { const row: string[] = []; for (let c = 0; c < used.getColumnCount(); c++) { const cell = used.getCell(r, c); const link = cell.getHyperlink(); row.push(link ? link.getAddress() : cell.getValue() as string); } vals.push(row); } sheet.getRangeByIndexes(0, used.getColumnCount(), vals.length, used.getColumnCount()) .setValues(vals); }

This writes extracted URLs into a new column beside your data. Run it on a copy first — it overwrites the target range.

Python: openpyxl bulk extractor

For hundreds of workbooks in a folder, a short script beats clicking through VBA:

from pathlib import Path from openpyxl import load_workbook for path in Path("invoices").glob("*.xlsx"): wb = load_workbook(path, read_only=True, data_only=True) for ws in wb.worksheets: for row in ws.iter_rows(): for cell in row: if cell.hyperlink: print(path.name, ws.title, cell.coordinate, cell.hyperlink.target)

cell.hyperlink.target is the raw URL. cell.hyperlink.location is the in-workbook anchor (#Sheet1!A1) when the link is internal — filter those out if you only want external URLs.

Google Sheets: bulk with a custom function

Extend the GETURL helper to walk a whole column. In Apps Script, bind a function that takes a range string and returns a 2D array of URLs:

function GETURLS(rangeA1) { const range = SpreadsheetApp.getActiveSheet().getRange(rangeA1); return range.getRichTextValues().map(row => row.map(rt => rt.getLinkUrl() || "") ); }

In the sheet, select a blank column and type =GETURLS("A2:A500"). Sheets spills the results. Empty string means no hyperlink on that cell.

Troubleshooting: why the URL comes back empty

When to use which approach

• One column, desktop Excel, macros allowed → VBA GetURL function.
• Excel for the web, macros blocked → Office Scripts.
• Google Sheets → Apps Script GETURL / GETURLS.
• Hundreds of files in a pipeline → Python openpyxl or unzip + XML parse.
• Locked-down environment, one-off → copy through Word trick.