Microsoft Excel get Hyperlinks from cells

I this article we’ll see how we can use a formula to extract the hyperlink from a Excel cell. There is no built in function and so we have to build our own. As you probably know, Excel supports VB scripting.

So first of all we have to build our function. Let’s name it getHyperlink(). It’s content will be the above:

Function getHyperlink(HyperlinkCell As Range)

    getHyperlink= Replace _

    (HyperlinkCell.Hyperlinks(1).Address, “mailto:”, “”)

End Function

This piece of code is basically taking the parameter cell and parse it’s Hyperlink to strip the “mailto:” reference.

How do we put this in Excel?

Well it’s kinda easy. Go to Tools/Macro/Visual Basic Editor (Alt F11 shortcut).

After starting the Visual Basic Editor, go to Insert/Module and paste int there the code.

After that close the VB editor and you are quite done. You can now use the formula in your sheets as shown below :

getHyperlink(A1)

Enjoy

Post a comment

Name (required)

Mail (will not be published) (required)

Website

Anti-spam text: (Required)*
To prove you're a person (not a spam script), type the security text shown in the picture. Click here to regenerate some new text.