Use this VBA function to extract the URL from a cell in Excel. This versatile VBA function works whether the hyperlink is entered in Excel using the “=HYPERLINK()” function or the “Insert > Hyperlink” menu. To top it off, the function can be called from another VBA macro or it can be entered as a formula directly into another cell in Excel. That’s what makes this function different from other solutions you may find online.
This helpful macro was submitted by a member of the wellsrPRO community, Mitch! Thank you, Mitch!
Macro to extract the URL from a cell
Created by Mitch
Function LinkLocation(rng As Range) 'DESCRIPTION: Get the formula url from hyperlink/formula or the insert/hyperlink method 'DEVELOPER: Mitch (wellsrPRO member) ' vars Dim sFormula As String, sAddress As String Dim L As Long Dim sHyperlink As Hyperlink, rngHyperlink As Hyperlinks ' cell formula sFormula = rng.Formula ' gets starting position of the file path. Also acts as a test if ' there is a formula L = InStr(1, sFormula, "HYPERLINK(""", vbBinaryCompare) ' tests for hyperlink formula and returns the address. If a link ' then returns the link location. If L > 0 Then sAddress = Mid(sFormula, L + 11) sAddress = Left(sAddress, InStr(sAddress, """") - 1) Else Set rngHyperlink = rng.Worksheet.Hyperlinks For Each sHyperlink In rngHyperlink If sHyperlink.Range = rng Then sAddress = sHyperlink.Address End If Next sHyperlink End If ' boom, got the hyperlink address LinkLocation = sAddress End Function
Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheets with just one click.
How to use the LinkLocation Macro
You can call the
These hyperlinks point to different URLs on the VBA Tutorials Blog and can be added using the “Insert > Hyperlink” menu, by right-clicking the cell and selecting “Hyperlink” or by using the native Excel =HYPERLINK() function, like this:
It doesn’t matter how your user inserts the hyperlink, the
It’s important to point out the
Calling the LinkLocation function using VBA
To call the
Sub ExtractURL() Dim strURL As String strURL = LinkLocation(Range("C3")) Debug.Print strURL End Sub
When you run this macro, the string
Notice how we only passed the
Calling the LinkLocation function from Excel
If all you wanted to do was to find out where a cell hyperlinks to, you could call the
In our example above, this would return the URL
Here’s what our extract URLs would look like if we placed our
I hope you enjoyed this tutorial. Send a big thanks to Mitch in the comments section if you found it helpful.
After that, please share this article with your friends on Facebook, Twitter, and Google+ and subscribe to my free wellsrPRO VBA Training Program.
About The VBA Tutorials Blog
The VBA Tutorials Blog was created by Ryan Wells, a Nuclear Engineer and professional VBA Developer. He created this site to help others learn to write macros in Excel. That's why he developed a unique 3-part free Excel training program to help others quickly learn VBA in a natural setting: right inside Excel.Follow