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)

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

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

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Ian Arman

Hey I'm @ianarman, I only clap about the cool stuff on @Medium!