Extract URLs from hyperlinks in Excel

If you copy a set of hyperlinks with friendly text from a website and paste them into Excel, each URL is embedded as part of the cell’s metadata.

This poses a challenge if you want to extract them, as there’s no native function for this purpose. Therefore, the options are limited.

Sure, you can right-click a cell and select Edit Hyperlink to access the URL to copy. But the manual nature of this makes it less than desirable when you have many to do.

Then there’s Power Query. Unfortunately, it isn’t able to dig out embedded URLs from hyperlinks, so the workaround is to pull them from the webpage’s raw HTML markup. That’s a bit fiddly, though.

What are we left with then? Ah, yes — VBA.

Press Alt + F11 to open the VBA Editor, create a new module, and copy and paste the following code:

VBA

Sub ExtractURLs()

    Dim cell As Range

        For Each cell In Selection
            If cell.Hyperlinks.Count > 0 Then
                cell.Offset(0, 1).Value = cell.Hyperlinks(1).Address
            End If
        Next cell

End Sub

Select a range with hyperlinks and then run the macro. This will populate the cells adjacent to the hyperlinks with the extracted URLs.

Many similar solutions I’ve seen across the web don’t include the If statement, which means if there’s a single cell without a hyperlink, it’ll throw an error. This snippet handles that scenario, though.

There is another way after all…

When I originally asked ChatGPT for an Office Scripts solution, it flip-flopped. First, it gave me a few lines of code that looked genuine but failed when I tried them. After sending screenshot after screenshot showing the errors, it still couldn’t come up with something that worked.

Then it started telling me repeatedly it wasn’t possible, as its API doesn’t provide a method to retrieve hyperlinks from cells. That’s when I believed it.

However, the next day I had another attempt, and this time it produced something that magically worked first time!

Go to Automate > New Script and copy and paste this code:

Office Scripts

function main(wb: ExcelScript.Workbook) {
    const r = wb.getSelectedRange(), rows = r.getRowCount(), cols = r.getColumnCount();
    for (let i = 0; i < rows; i++)
        for (let j = 0; j < cols; j++) {
            const addr = r.getCell(i, j).getHyperlink()?.address;
            if (addr) r.getCell(i, j).getOffsetRange(0, 1).setValue(addr);
        }
}

Select Run to see the same thing happen as before.

Hopefully, either of these code snippets will save you time when you’re next dealing with hyperlink-heavy data!