This should do the trick.
Dim rngRange As Range
For Each rngRange In Selection
If Trim(rngRange.Value) <> "" Then
Anchor:=Cells(rngRange.Row, rngRange.Column), _
If you have to do it a lot, I'd hook it up to a keyboard shortcut.
To make this happen in Excel 2010 (compliments of @Excellll)
- Go to "View" and then click the "Macros" button.
- This will open a dialog box asking for the name of a new macro. Enter "Test" and click "Create".
- This will open the VBA editor. Copy the code above into this new window.
- Now go to your spreadsheet and select the range of cells with the hyperlinks you want to fix.
- Go back to the VBA editor. Press F5. This will execute the code.