Extract URL from Excel Links

Here’s a great #Hack for Microsoft Excel fanatics!

Let’s say you have a large range of data with embedded hyperlinks.

Like this range:

Assuming for the moment that someone else created these links — and you the excel fanatic would have obviously created the adjacent column in the first place.

Alas… pitter-patter.

Here’s your opportunity to learn something new!

Here’s the solution to creating the adjacent column which “resolves” the hidden links.

Interestingly enough Microsoft has not created an embedded function for this — and probably won’t (at least in the near future) since it’s a simple few lines of VB code.

OpenOffice Calc while also a great alternative for it’s lightweight functionality, similar user interface and it’s the ability to create and open CSV files with more ease than Microsoft Excel.

The functionality of “extracting hyperlinks” doesn’t exist on that platform (yet) — I’d suggest posting a request to the community development improvement forum.

With Microsoft Excel, however, this will save you countless hours of tedious right-clicking with an additional CTRL+V.

While muscle memory might speed things up.

This VB hack will give you a simple function

Open Excel

Open up a new workbook.

• Get into VBA (Press Alt+F11)
• Insert a new module (Insert > Module)
• Copy and Paste the Excel user-defined function below
• Get out of VBA (Press Alt+Q)

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

Place this newly created code in the cell which is adjacent to the cell with the link.

=GetURL(A1)

Thanks for reading my Medium story.

If you found this useful, consider clapping and don’t forget to hit the SUBSCRIBE button to be notified of more useful articles like this one!

Ian Arman