Function get URL in Excel

Create in macro like function below:

Function GetURL(rng As Range) As String
On Error Resume Next
GetURL = rng.Hyperlinks(1).Address
End Function

then open excel, type in a cell "=GetURL(A1)" for link/url you will capture


after that, you can correction the URL using macro below.

For URL = 6 To 329
AddressName = Cells(URL, 23).value
Range("W" & URL).Select

Selection.Copy
Range("L" & URL).Select
ActiveSheet.Paste
Application.CutCopyMode = False
AddressURL = Cells(URL, 27).value

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=AddressURL, _
TextToDisplay:=AddressName
Next