Excel - Replace hyperlink URLs in bulk using Find and Replace

At times, spreadsheets will move that will break existing links within other sheets. This is a quick how-to on how to change those in bulk. 

Steps

Here are the steps needed to update the hyperlinks in bulk. You will need the path and possibly the cells of the other file that moved, and the sheet where the existing hyperlink reside open. In this example, File 1 moved to a new location. File 2 has the broken hyperlinks that need to be replaced.

Step-by-Step

  1. Copy path of new location for moved document. 
    1. In this example, the path was \\new file path is
  2. In Excel document referencing old location, select range of cells  
    1. In this example, cells B2 to B9 were selected
  3. Press Ctrl+h for Find and Replace
  4. Enter old path into the Find
    1. Old path can be copied by selecting 1 cell with old path and copy part that needs updated
    2. In this example, the path changed from a map network drive Y:\Other\ and the file name changed TestFile2SMB.xlsx so both were selected
    3. Uploaded Image (Thumbnail)
  5. Enter new path into the Replace with
    1. In this example, the Replace with was the SharePoint Site, path, and file name
      1. ='https://dartmouth.sharepoint.com/sites/CTCTeam/Shared Documents/CTC Team/Consultants/Joel/[TestFile2.xlsx]
    2. Uploaded Image (Thumbnail)Search Within Selection should be selected
    3. An easy way to find the replace with is to do 1 cell manually, where the excel cell with the formula is entered with = then the second file with the destination cell is selected.
      1. In this example, clicked Cell B2 in TestFile1, typed =, then clicked A1 in TestFile2
  6. Replace All

Print Article