Doing a search and replace on all embedded hyperlinks in a spreadsheet

The key issue is that Excel’s standard Find & Replace doesn’t search the actual hyperlink behind a cell’s displayed text. It only searches the visible content. So if a cell says "Click here" but links to file://fs-prd/etc/..., a normal search for "file://fs-prd" won’t find it.

You can create a list (with VBA code) to display all the hyperlinks in a sheet and then modify them manually. The code works like find function.

Sub ListHyperlinks()
    Dim ws As Worksheet
    Dim hl As Hyperlink
    Dim outputRow As Long
    
    ' Change this to the sheet where you want the list
    Set ws = ActiveSheet
    
    ' Start listing from row 1 in columns A and B
    outputRow = 1
    ws.Cells.Clear ' optional: clears sheet to make room
    
    ' Header
    ws.Cells(outputRow, 1).Value = "Text Displayed"
    ws.Cells(outputRow, 2).Value = "Hyperlink URL"
    outputRow = outputRow + 1
    
    ' Loop through all hyperlinks
    For Each hl In ws.Hyperlinks
        ws.Cells(outputRow, 1).Value = hl.TextToDisplay
        ws.Cells(outputRow, 2).Value = hl.Address
        outputRow = outputRow + 1
    Next hl
    
    MsgBox "All hyperlinks have been listed in columns A and B."
End Sub

If you open the file in Excel Desktop App (not in the browser/Excel for the web), the macro will run normally. Macros do not run in Excel for the web, so you must use the desktop version.

My answers are voluntary and without guarantee!

Hope this will help you