Forum Discussion

tedmclaughlin's avatar
tedmclaughlin
Copper Contributor
Sep 29, 2025

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

We are in the process of migrating departments from file shares, to Sharepoint Online, and within many of the spreadsheets there are embedded links directly to files whose paths are changing.

 

We are trying to do a search and replace to get rid of "file://fs-prd/etc/etc/etc" and replace it with "https://mycompany.sharepoint.com/etc/etc".  We think this should work, but for the life of us we can't get it to.

 

The problem is that all of these links are embedded behind other text, such as "click here".  We can do a search and replace on "click here", but not on the actual link behind it.

On the Find or Replace window, we've tried selecting all of the options in the "look in" drop down, but none of them work.

We've been trying different ways of putting in the link we are looking for, such as "='file://" or just "file://", etc, but we haven't found anything that will bring us back any actual link.

If you know how to do this, please let me know.  Or if you know of a way to get rid of the "click here" and show the hyperlinks we'd be happy with that as well.

Thanks in advance for any help!

excel

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Oct 02, 2025

    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

Resources