Bulk edit hyperlinks in Excel

Making a link in Excel by hand or with the HYPERLINK function is easy. But if you need to edit dozens or hundreds of links at once, it gets tedious. Especially if the links were inserted not with a formula, but through Insert → Link, or pasted from a browser or a document.

In that case, Excel has no built-in way to edit links in bulk: Find and Replace changes only the displayed text, not the address, so you’re left with editing each link one by one. Here is how to do it in 4 steps.

Good news: the “Extract hyperlinks” and “Build hyperlinks” procedures are available in the free version of !SEMTools.

1. Extract the link addresses

The first step is to get the hyperlink addresses out of the cells. If the links were inserted manually rather than with a formula, Excel won’t let you copy the URL, only the displayed text.

The Extract hyperlinks tool on the !SEMTools tab gets the URLs from all selected cells. It works with links of any type: websites, local documents, email links, even links on pictures.

Without the add-in, you can do the same with a tiny VBA function. Press Alt+F11, Insert → Module, paste the code and use =GETURL(A2) on the sheet:

Function GETURL(cell As Range) As String
    If cell.Hyperlinks.Count > 0 Then GETURL = cell.Hyperlinks(1).Address
End Function

The workbook then has to be saved as .xlsm, and the formula works only where macros are enabled.

2. Change the link text

If you only need to change the displayed text, not the address itself, there are several ways. Built-in Excel tools:

• Find and Replace (Ctrl+H) for a fragment of text;
• formulas with SUBSTITUTE, TEXT, CONCATENATE, RIGHT, LEFT and so on in a helper column.

Or the !SEMTools procedures that replace or transform text in bulk right in place. That’s handy if you want, for example, to replace “Report” with “Analytics”, or add text to the beginning of the cells, like “Download: ” before the link.

3. Change the URLs

Editing the URLs themselves is more delicate. Especially if the links lead to reports where the URL contains client, date and filter parameters, for example:

https://example.com/report?client=54152245&period=month&group=day&currency=USD

Say you want to change the client ID from 54152245 to another one, the period from month to yesterday and the grouping from day to hour. First extract the URL (step 1), then use:

• SUBSTITUTE for an exact replacement of a parameter;
• VLOOKUP to pull parameters from a reference table by client name;
• IF, TEXT and other functions for more complex logic;
• REGEXREPLACE, when the old value of a parameter is unknown or differs from row to row.

Here is a formula that changes the query parameters. The URL is in B3, the new client ID in E3, the period in F3, the grouping in G3:

=REGEXREPLACE(REGEXREPLACE(SUBSTITUTE(B3,"client=54152245","client="&E3),"period=[^&]*","period="&F3),"group=[^&]*","group="&G3)

The pattern “period=[^&]*” means “period=” followed by any characters up to the next “&”, so it replaces the value whatever it was. REGEXREPLACE is built into Excel 365, and in older versions it works if you have !SEMTools installed. More about patterns in the article on regular expressions in Excel.

This way you can set up a template link and generate dozens of personalized URLs for reports, client dashboards and so on.

4. Build the hyperlinks

Once the URLs are edited, you need to turn them back into clickable links. The HYPERLINK function does it, with the address in B2 and the text in A2:

=HYPERLINK(B2,A2)

But the result is a formula: if you delete the source columns, the links break, and the cells look like formulas, not like normal links. To get regular hyperlinks and keep the source format, use the Build hyperlinks tool on the !SEMTools tab, in the Join/Split group.

It creates links from a range of three columns: text, link, tooltip. It returns a column of active hyperlinks on a new sheet. If you don’t need tooltips, leave that column empty, but select it anyway:

And to complete the picture, you can open all these links at once and check what the reports show: !SEMTools has a procedure for that too :)

Common mistakes

• Find and Replace changed the text, but the links still lead to the old address. It doesn’t touch hyperlink addresses. Extract the URLs, edit them and build the links again.
• The HYPERLINK formula shows #VALUE!. The address is longer than 255 characters: HYPERLINK can’t handle such long URLs, while regular hyperlinks can.
• Links to local files stopped working on another computer. They contain a path on your disk. Use relative paths or a shared network folder.

Summary

I hope you found what you were looking for on this page and were able to edit your hyperlinks in bulk. The workflow is always the same: extract the addresses, change the text or the URLs with formulas or procedures, and build the links back. With !SEMTools, the first and the last steps take one click each.

You may also be interested in other join and combine tools.