Selected Answer
Hello Sathya,
Your question isn't clear on whether the original URL is in a hyperlink or a worksheet cell. The UDF (User Defined Function) below can extract the full address or the nett URL from a hyperlink. It must be pasted to a standard code module which you must insert in the VBA editor. None of the pre-existing code modules is suitable. Once code is added to your workbook it must be saved as macro-enabled (xlsm format).
Function GetURL(Cell As Range, _
Optional Shorten As Boolean)
' 07 Jun 2019
Dim Fun As String
Dim Sp() As String
Application.Volatile
On Error Resume Next
' ignores all but the first cell of a multi-cell range
Fun = Cell.Cells(1).Hyperlinks(1).Address
If Err Then
Fun = "No hyperlink"
Err.Clear
Else
If Shorten Then
On Error GoTo 0
Sp = Split(Fun, "//")
Fun = Split(Sp(UBound(Sp)), "/")(0)
End If
End If
GetURL = Fun
End Function
In the Worksheet the UDF is called like any other function, for example,
=GetURL(A2)
or
=GetURL(A2,True)
Copy down as you would with any built-in function. If the Shorten parameter is True the function will return the nett URL without extensions. A2 is a cell. It also can be a range of several cells, like A2:A10. But the function will look at only the first cell. If the cell doesn't contain a hyperlink "No hyperlink" is returned.
In case you don't need to extract the URL from a hyperlink the formula below will shorten an address typed in a cell (B2 in my example).
=LEFT(B2,FIND("/",B2,IFERROR(FIND("//",B2),-1)+2)-1)
