Replace generic hyperlink text with value from table

[simmitop]

Hello all,
we have a document management system that exports data in the format shown in the picture.

I need to batch replace the generic "Locator URL" hyperlink texzt with the actual name of the file.

Is this possible at all?

 

[jkpieterse]

This little macro will scan through all hyperlinks on a sheet and place their target addresses to the right of the cell containing the link and in the adjacent column it will also add the HYPERLINK formula to recreate the link:

Sub DisplayHyperlinkAddresses()
    Dim lnk As Hyperlink
    For Each lnk In ActiveSheet.Hyperlinks
        lnk.Range.Offset(, 1).Value = lnk.Address
        lnk.Range.Offset(, 2).Formula = "=HYPERLINK(""File:""&" & lnk.Range.Offset(, 1).Address(False, False) & ")"
    Next
End Sub