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

Validate if URL Exist?


Function Test_URLExists()
  Dim url As String
  
  url = "http://www.aaaaaaaaaaaaaaaaaa.com" 
  MsgBox url, vbInformation, URLExists(url)
  
End Function

Function URLExists(url As String) As Boolean
    Dim Request As Object
    Dim ff As Integer
    Dim rc As Variant
    On Error GoTo EndNow
    Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")
    With Request
      .Open "GET", url, False
      .Send
      rc = .StatusText
    End With
    Set Request = Nothing
    If rc = "OK" Then URLExists = True
    Exit Function
EndNow:
End Function