0

I would like some help with removing/replacing a wildcard character in an excel hyperlink. Logically it seems very easy but it's beyond my abilities.

I have a spreadsheet with hyperlinks to PDF documents. The hyperlinks contain the "#" character and that stops the file path from working. In the hyperlink I simply need to change the "#" to "%23" and the link works. I don't want to do this manually because of the amount of links. Is there any way of achieving this with VBA. It seems easy enough to change a file path but searching a hyperlink and changing the # doesn't seem to be possible.

All the hyperlinks are in column A.

Community
  • 1
  • 1
Xaser
  • 5
  • 3
  • Is the hyperlink in a Hyperlink() function or has it been manually inserted with Insert > Hyperlink? – teylyn Feb 21 '17 at 05:16
  • Hi teylyn, It's not a hyperlink function of the sheet as in it does not appear in the formula bar. Right click > edit hyperlink is the only why to see it. – Xaser Feb 21 '17 at 05:45

1 Answers1

0

Excel treats text to the left of the # as the .Address and to the right as the .SubAddress - as it suggests an anchor type link. You'd need to repair this on each link like so:

For Each lk In Sheets("YourSheetName").Range("A:A").Hyperlinks
    If lk.SubAddress <> "" Then
        lk.Address = lk.Address & "%23" & lk.SubAddress
        lk.SubAddress = ""
    End If
Next
CLR
  • 11,284
  • 1
  • 11
  • 29