VBA Decode Hyperlink Not grabbing complete URL

[bubbapost]

Hello,

I'm using the code below to decode a list of hyperlinks in column A however, id doesn't grab the full URL.

All of these links go to different bookmarks within the same website page.

Example:
The full URL is: https://www.ecfr.gov/cgi-bin/text-i...c=true&node=pt42.4.435&rgn=div5#se42.4.435_12
My code only gives me: https://www.ecfr.gov/cgi-bin/text-i...b91d50d51553&mc=true&node=pt42.4.435&rgn=div5

I right click on the link in column A and it has the full URL.

Sub DecodeEachHyperlink()
Dim hl As Hyperlink
Dim ThisURL As String
For Each hl In ActiveSheet.Hyperlinks
    ThisURL = hl.Address
    hl.Parent.Offset(0, 1).Value = ThisURL
Next hl
End Sub

Can anyone help me please?

Thank you in advance!
Andy

 

[bubbapost]

Updated example:

The Full URL is: -https://www.ecfr.gov/cgi-bin/text-idx?SID=7cabcefca55c0c40ab13b91d50d51553&mc=true&node=pt42.4.435&rgn=div5#se42.4.435_12-
My code only gives me: -https://www.ecfr.gov/cgi-bin/text-idx?SID=7cabcefca55c0c40ab13b91d50d51553&mc=true&node=pt42.4.435&rgn=div5-

 

[John_w]

Try one of these:

        hl.Range.Offset(0, 1).Value = hl.Range.Value
        hl.Range.Offset(0, 2).Value = hl.Address & "#" & hl.SubAddress

 

[bubbapost]

Thank you, it worked perfectly!

Try one of these:

        hl.Range.Offset(0, 1).Value = hl.Range.Value
        hl.Range.Offset(0, 2).Value = hl.Address & "#" & hl.SubAddress