I had the same issue as @SAL
I found that the
.Hyperlink object actually splits the address into
.SubAddress if it encounters a delimiter. "#" is one such delimiter (I don't know if there are others).
See @gilly3 's comment above and this other StackOverflow question:
hyperlinks(1).address only returning a partial address
Also Microsoft's (incomplete) documentation on .SubAddress:
One thing not mentioned anywhere is that the "#" delimiter is not included in either
.SubAddress so if you want an address that will actually work as a full URL, you have to add the "#" delimiter back in.
With that, I modified @Matthew Lock's code as follows, also including the possibility of multiple hyperlinks being stored from @Igor O and my addition of "#":
Function getURL(rng As Range) As String
On Error Resume Next
fullURL = ""
For Each HL In rng.Hyperlinks
If Len(HL.SubAddress) > 0 Then
fullURL = fullURL & HL.Address & "#" & HL.SubAddress & " "
fullURL = fullURL & HL.Address & " "
getURL = fullURL
This should return a text string of all hyperlinks in full (I added a space at the end so if there's more than one, they are separated).