VBA & Macro Problems

Convert Excel HYPERLINK Formulas to Permanent Hyperlinks with VBA

Rana GarciaRana Garcia Nov 12, 2026 869 views

Question details

The user needs to convert a large number of HYPERLINK formulas into permanent clickable hyperlinks without leaving the formulas in the workbook.

Convert Excel HYPERLINK Formulas to Permanent Hyperlinks with VBA
Product
Excel or WPS Spreadsheet
Device & OS
not provided
Scenario
A workbook contains thousands of HYPERLINK formulas, including formulas that calculate the URL and display text. The user wants to reduce workbook processing overhead while keeping every valid link clickable.
Observed behavior
Pasting values removes the formulas but also removes the clickable hyperlink behavior, so the links need to be recreated as standard hyperlink objects.
Before you start

Save a backup copy of the workbook and test the macro on a small duplicate sheet first. Confirm that the target cells contain formulas with HYPERLINK and that the calculated link addresses are valid.

Solution 1Recommended

Use VBA to replace HYPERLINK formulas with permanent hyperlink objects

Use this method when the workbook contains many HYPERLINK formulas and you need to preserve both clickable links and displayed text without retaining the formulas.

The macro finds cells containing HYPERLINK formulas, evaluates the address expression, records the displayed result, removes the formula, and adds a standard hyperlink to the same cell. It skips formulas that do not return a usable address or display text.

1
Open the VBA editor

In Excel or WPS Spreadsheet, press Alt+F11, choose Insert > Module, and create a standard VBA module.

2
Paste and run the conversion macro

Paste the following code into the module, return to the workbook, press Alt+F8, select ConvertHyperlinkFormulas, and click Run. Select the worksheet that contains the formulas before running it. Sub ConvertHyperlinkFormulas() Dim ws As Worksheet, rng As Range, c As Range Dim f As String, addrExpr As String, address As Variant Dim displayText As String, subAddress As String Set ws = ActiveSheet On Error Resume Next Set rng = ws.UsedRange.SpecialCells(xlCellTypeFormulas) On Error GoTo 0 If rng Is Nothing Then MsgBox "No formula cells were found on the active sheet." Exit Sub End If Application.ScreenUpdating = False For Each c In rng.Cells f = c.Formula If InStr(1, f, "HYPERLINK(", vbTextCompare) > 0 Then If GetHyperlinkAddressExpression(f, addrExpr) Then On Error Resume Next address = ws.Evaluate("=" & addrExpr) If Err.Number = 0 Then displayText = CStr(c.Value2) If Len(Trim$(CStr(address))) > 0 And Len(displayText) > 0 Then c.ClearContents If Left$(CStr(address), 1) = "#" Then subAddress = Mid$(CStr(address), 2) ws.Hyperlinks.Add Anchor:=c, SubAddress:=subAddress, TextToDisplay:=displayText Else ws.Hyperlinks.Add Anchor:=c, Address:=CStr(address), TextToDisplay:=displayText End If End If End If Err.Clear On Error GoTo 0 End If End If Next c Application.ScreenUpdating = True MsgBox "Hyperlink formula conversion is complete." End Sub Private Function GetHyperlinkAddressExpression(ByVal formulaText As String, ByRef addressExpression As String) As Boolean Dim p As Long, i As Long, depth As Long, commaPos As Long Dim inQuotes As Boolean, ch As String p = InStr(1, formulaText, "HYPERLINK(", vbTextCompare) If p = 0 Then Exit Function p = p + Len("HYPERLINK(") depth = 1 For i = p To Len(formulaText) ch = Mid$(formulaText, i, 1) If ch = """" Then inQuotes = Not inQuotes If Not inQuotes Then If ch = "(" Then depth = depth + 1 If ch = ")" Then depth = depth - 1 If ch = "," And depth = 1 Then commaPos = i Exit For End If If depth = 0 Then Exit Function End If Next i If commaPos > 0 Then addressExpression = Trim$(Mid$(formulaText, p, commaPos - p)) GetHyperlinkAddressExpression = (Len(addressExpression) > 0) End If End Function

3
Verify the converted links

Click several converted cells and confirm that they open the expected destinations. Then select a converted cell and check that the formula bar shows hyperlink text instead of the original HYPERLINK formula.

Keep the original workbook until verification is complete: The macro changes formulas in place and skips cells whose address cannot be evaluated. Compare the converted sheet with the backup before deleting the original formulas.
WPS Spreadsheet solution

Replace HYPERLINK formulas with permanent clickable links in WPS Spreadsheet

WPS Spreadsheet can help streamline large workbooks by running a VBA conversion macro where macro support is available. Convert the calculated addresses into standard hyperlinks, verify the results, and remove the formula overhead while keeping the workbook usable.

  1. 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Spreadsheet, and open the workbook containing the HYPERLINK formulas.
  2. 2. Open the macro editor: Press Alt+F11, choose Insert > Module, and paste the conversion macro from this guide if VBA is available in your WPS installation.
  3. 3. Run the conversion: Return to the worksheet, press Alt+F8, select ConvertHyperlinkFormulas, and click Run on the active sheet.
  4. 4. Check and save the result: Test several converted cells, confirm that the URLs open correctly, and save the cleaned workbook in XLSX format.
Preserves clickable links while removing thousands of HYPERLINK formulasSupports bulk spreadsheet cleanup through VBA-enabled workflowsProvides a familiar spreadsheet interface for reviewing and testing converted linksMaintains compatibility with Microsoft Excel XLSX and XLS workbooks
microsoft office alternative - wps office

Frequently Asked Questions

Will pasting values preserve the clickable HYPERLINK behavior?

No. Pasting values normally keeps only the displayed text. Use a VBA macro or Insert > Hyperlink to create permanent hyperlink objects after removing the formulas.

Can the macro process formulas that calculate the URL with INDEX and XMATCH?

Yes, if the address expression can be evaluated by the spreadsheet application. The macro evaluates the expression inside HYPERLINK and then creates a standard hyperlink from the result.

What happens to blank cells containing HYPERLINK formulas?

The macro skips cells whose calculated address or displayed text is blank. This prevents empty formulas from becoming unnecessary hyperlink objects.

How can I confirm that the formulas were removed?

Select a converted cell and look at the formula bar. A successful conversion shows hyperlink text rather than a formula beginning with HYPERLINK.

Can I undo the conversion after running the macro?

You may be able to use Undo immediately, but the safest method is to keep a backup copy and test the macro on a duplicate worksheet before processing the original data.