How To Extract A URL From A Hyperlink On Excel

How To Extract A URL From A Hyperlink On Excel

Many of the people find it difficult to extract the URLs from the list of text. It is as easy as anything. There are two different ways of doing it.

At first, let’s talk about the manual way of doing it.

  • Place the pointer on hyperlink and right click on it.

  • Choose Edit Hyperlink option from the menu that appears. A complete Edit Hyperlink box will be in front of you.

  • Select the complete URL and copy it from the field.

  • Now press the ESC button to exit the dialog box.

  • Now type CTRL +V to paste the copied content where needed.   

 

Now, if you have a huge number of hyperlinks, this manual task is not going to work for you as it will be very time consuming for you. There is another option for you to extract the URLs quite easily. Below is an example that can be beneficial if you are looking to extract hyperlinks from where it is copied in excel. There are two different options for you here as well.

To do this activity only for a single time

  • Create new workbook

  • ALT+F11 to get into it.

  • Go to Insert option and Select Module

  • Copy the below mentioned function and paste it.

  • Press the F5 button and Run

  • ALT + Q to exit VBA.  

 

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

 

Now if you want to add some more links to the sheet and want to maintain it, the first four steps are same as mentioned above. You need that particular syntax for that very function. GetURL(cell,[default_value])

 

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