Extract URL from Excel using VBA not extracting after hash

Question

I am using the VBA method to extract the URL from a cell:

Sub ExtractHL()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
Next
End Sub

This is pulling everything in the URL before the # example: If the whole URL is https://www.w3.org/TR/html4/sgml/entities.html#h-24.4.1 my results will only show: https://www.w3.org/TR/html4/sgml/entities.html missing the #h-24.4.1 Does anyone have a VBA solution to extract the whole URL including the hash and everything after?

Answer 1

I found an answer and explanation on another site. The part of the link after the # is regarded as a subaddress. The issue is resolved by including the .SubAddress.

Function GetURL(cell As range, _
Optional default_value As Variant)
'Lists the Hyperlink Address for a Given Cell
'If cell does not contain a hyperlink, return default_value
If (cell.range("A1").Hyperlinks.Count <> 1) Then
GetURL = default_value
Else
GetURL = cell.range("A1").Hyperlinks(1).Address & "#" & cell.range("A1").Hyperlinks(1).SubAddress
End If
End Function

Answer 2

I usually create a function. In workbook module paste below function:

Function HLink(rng As Range) As String
'extract URL from hyperlink
  If rng(1).Hyperlinks.Count Then HLink = rng.Hyperlinks(1).Address
End Function

Now use the function as a formula, like so:

1. in A1 you have the URL
2. in A2 you have the new formula =hlink(A1)

PS: original from Rick Rothstein

Answer 3

This worked for me when I could predict where the pound/hash symbol is placed. This is an iterative of another "GetURL" function I found online.

Function GetURLs(HyperlinkCell As Range)

    GetURLs = HyperlinkCell.Hyperlinks(1).Address + "#" + HyperlinkCell.Hyperlinks(1).SubAddress

End Function