Loading...Your column of data in excel can contain anything from normal texts to numbers and even hyperlinks. If you type in any URL in the standard format, Excel automatically hyperlinks the URL text to the respective link. But sometimes, the links are associated or are hyperlinks of some other anchor text, rather than the link itself. In such case, it won’t be practical to individually click each link or to make their list. Or, another scenario is that you may need to extract links from a long list of hyperlinks you found on a webpage. Well if need to extract hyperlinks from excel in hundreds or even thousands, here is a nifty little trick.
Open the excel document that you need to extract links from, or simply copy your list of hyperlinks from a webpage to an excel document. We will actually create a macro that will do the trick for you.
You will first need to bring up the Developer tab on your Excel if it is already not there. To do that, click on the Office button and select “Excel Options”. Now click on the “Show Developer tab in the Ribbon” checkbox and click OK. You will now see the Developer tab at the top menu.
Next, click on the Visual basic button. A new window will open up. Now go to the Insert Menu and then go to Module. Now paste the following into it.
Function ExtractHyperlink(pRange As Range) As String
Dim ST1 As String
Dim ST2 As String
If pRange.Hyperlinks.Count = 0 Then
Exit Function
End If
ST1 = pRange.Hyperlinks(1).Address
ST2 = pRange.Hyperlinks(1).SubAddress
If ST2 <> “” Then
ST1 = “[” & ST1 & “]” & ST2
End If
ExtractHyperlink = ST1
End Function
After pasting the above code, close the window from File>Close.
Your function is now ready to be applied on your Excel Document. Select a cell next to the first cell, i.e. B1 for convention and click on the fx sign in the formula bar. Now select the “user defined functions” option from the dropdown from the box that shows up. This will show the function called ExtractHyperlink in the list. Click on OK; this will bring up a dialogue box for inserting the function arguments. In the PRange box, you will need to enter any cell name viz A1, B1, A2, B2 etc. You can also simply click on a cell, say A1 and click OK. You will now see that the link from the hyperlinked text in A1 cell appears on A2. Now double click on the small dot at the bottom right corner of A2 to apply the function on all hyperlinks in the first column. Thus all the hyperlinks will be extracted and shown in the second column.
Therefore using this simple function, you can easily extract hyperlinks in Excel.