Extract URL from Hyperlink with an Excel Formula

Let’s say you have a hyperlink in a cell in Excel. The hyperlink may have friendly text, such as Click Here, but when you click the link it takes you to a URL such as https://www.excel-university.com. Now, let’s say you want to extract that URL from the hyperlink using an Excel formula.

Well … to my knowledge, there isn’t a built-in function to accomplish that. But, we can actually create our own custom function, and even name it URL if we’d like, using a few lines of code. I’ll walk you through each step so it will be easy to implement. And thanks to my friend Cary who asked how to extract a url from a hyperlink which led to this post!

Overview

Before we get too far, let’s confirm what we are trying to accomplish. We have a hyperlink, or maybe several hyperlinks, in some Excel cells. Like this:

Excel workbook with Hyperlinks ... we want to extract the URL

We would like to be able to write some type of formula like =URL(B7) to extract the underlying URL from the links, like this:

The goal of extracting the urls from these hyperlinks with an excel formula

Although (at the time of this writing) Excel doesn’t have a built-in URL function, we can create our own custom URL function using a few lines of code.

I’ll break the entire process down into bite-sized steps. Ready? Let’s do this.

We’ll accomplish our objective with the following steps:

  • Create the URL function
  • Use the function to extract the URL
  • Save workbook as XLSM

Let’s start with creating the custom URL function.

Create the URL function

We’ll need to add our custom URL function to the workbook.

Note: if you want to skip this step, I already created the URL function in the Sample File below. So, rather than creating it yourself, you can certainly just download the workbook and get on with it. But, if you are curious about how it works, keep reading and I’ll explain the details.

The first thing we need to do is open the Visual Basic Editor. This can be accomplish by using the Alt+F11 keyboard shortcut in Excel for Windows, and I believe Opt+F11 (or Fn+Opt+F11) in Excel for Mac.

Next, we need to insert a new Module into the workbook. To do so, locate the workbook in the Project Explorer panel … it should say something like VBAProject (Workbookname) like this:

vba editor

Note: if you don’t see the Project Explorer panel, use the Ctrl+R keyboard shortcut to toggle it on.

Then, right-click the workbook name and select Insert > Module. You’ll see a new Module1 appear in a new folder called Modules, like this:

create a new module to store the custom function that extracts the url

Next, double-click Module1 so it opens. You’ll see a blank window that feels a little bit like a word-processor because you can type stuff there. You could type the custom function code, but it will be faster to copy/paste. So, copy this VBA code:

Function URL(Hyperlink As Range)
  URL = Hyperlink.Hyperlinks(1).Address
End Function

And then paste it into Module1. It should look like this:

this custom function extracts the url from a hyperlink using an excel formula

Believe it or not … the hard part is done!!!!!!!!!!!!!!!!

You can now toggle back to your Excel screen or close the Visual Basic Editor.

With the custom function complete, it is time to use it to extract the URLs from our hyperlinks.

Note: custom functions are stored inside workbooks rather than inside of the Excel application. This is good because other people that open the workbook can use the custom function. But, it also means you’ll either need to use this workbook for other URL extraction projects or create the custom function in other workbooks as needed.

To use the custom URL function, simply include it in a formula as you would with built-in functions. So, if our hyperlink was in B7, we could write the following formula in C7 to retrieve the URL from the hyperlink:

=URL(B7) 

Hit Enter, and bam…

Resulting function that retrieves the url from the hyperlink

We can also fill the formula down, and bam…

Fill the formula down to extract the url from each hyperlink in the range with an excel formula

With our mission accomplished, we need to chat about file types and custom code.

Save workbook as XLSM

When you try to save or close the workbook, you’ll probably get a message like this:

save as a macro-enabled workbook to reuse the code later

This is basically telling you that if you want to be able to use the custom URL function in the future, you’ll need to save it as an XLSM file type instead of the default XLSX which is a macro-free file type.

So, first click No to the dialog above, and then change the Save as type option to Excel Macro-Enabled Workbook (*.xlsm) like this:

excel macro-enabled workbook xlsm

Once done, it means that the custom function will be successfully saved in the workbook. In the future, if you (or anyone else) opens the file you will have the ability to use the custom URL function to extract the URLs from hyperlinks.

When you (or anyone else) opens the workbook in the future, you may receive a security warning like this:

security warning to enable macros to use it in the future

Be sure to Enable Content so that the URL function will work.


FREE: Excel Speed Challenge

If you enjoyed this post, please check out our free Excel speed challenge.

Watch one short Excel video a day for 5 days. Total video time is only 45 minutes. Learn the Excel skills that can help you save an hour a week.


Conclusion

Well, that is one way to extract a URL from a hyperlink using an Excel formula. If you have any other preferred methods or improvements to this one, please share by posting a comment below … thanks!

Sample file

Posted in , ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.

30 Comments

  1. Michael V Bernot on February 23, 2022 at 9:26 am

    Can this function be saved in “Personal” and be available for all workbooks….or do I have to create this function in each workbook? Thank you.

    • Jeff Lenning on February 23, 2022 at 9:29 am

      I believe the VBA code needs to reside in the workbook from which it is called … meaning, if you save it in your personal macro workbook other workbooks won’t be able to use it. Sorry about that! But, add-ins do seem to support custom functions so there may be way using that approach.
      Thanks
      Jeff

      • happydz on June 6, 2022 at 7:37 pm

        thank you for sharing this trick for us
        Is there a way to make the extracted URLs clickable without me clicking twise on the cell in column C?

        • Stjärttarmelof on June 27, 2022 at 10:27 am

          =HYPERLINK(URL(A1))

  2. LM Weigel on February 23, 2022 at 11:21 am

    As always, this is really helpful! I always learn a lot from Excel University. Thanks!

  3. Edil Poulina on February 25, 2022 at 3:45 pm

    Thanks Jeff. Very nice, cool and helpful.
    Don’t we need to include some error trapping into the code, for just in case of? (E.g. wrong reference passed as argument to the URL function?)

  4. Keith Rutter on March 9, 2022 at 1:35 pm

    You can use a function from your Personal.xlsb file. The following url links to the explanations.
    https://www.myonlinetraininghub.com/creating-a-reference-to-personal-xlsb-for-user-defined-functions-udfs

  5. Libby on April 18, 2022 at 5:13 pm

    omg – this saved me about 2 hours of work extracting URLs – thank you!

  6. Rebekah on May 11, 2022 at 4:26 am

    This is brilliant and you are a legend. Thank you. You saved me hours.

    • Jeff Lenning on May 11, 2022 at 8:28 am

      Glad it helped … and thanks for your kind note 🙂

  7. Amer on May 26, 2022 at 4:50 am

    Really helped me fix what I wanted very quickly – thank you Jeff!

  8. Isabelle T on August 5, 2022 at 1:00 am

    Thank you so much for the useful tip, Jeff! It’s easy to apply and very helpful!

  9. Jason on August 13, 2022 at 9:47 am

    My cell looks like this:

    =HYPERLINK(“https://www.example.com/gis.html?parcelid=352915501840080080”, “Map”)

    And when I use the Function it returns #VALUE!
    Any thoughts on how to fix this?

    • Neil on February 28, 2023 at 5:52 am

      Jason — Were you able to resolve this? I’m experiencing the same situation. Instead of the URL, I’m getting #VALUE!

    • Billy on June 26, 2023 at 4:58 pm

      I’m having the same problem, and I followed all the instructions and copied/pasted the formula.

      • K on August 9, 2023 at 2:08 pm

        Same issue here; code does not work on on hyperlink=(,) cells at all.

        • Cristina on September 20, 2023 at 9:22 am

          you need to use =URL() as that’s the name assigned to the formula 🙂

  10. Chris on August 23, 2022 at 1:57 pm

    Thanks! That was super helpful!!

    • Jeff Lenning on August 23, 2022 at 1:58 pm

      Thanks … glad it helped 🙂

  11. JC on August 27, 2022 at 2:30 pm

    Jason, I am getting something even weirder.

    I am extracting a hyperlink from a string, and then recreating one with a friendly name of my choosing.

    With a friendly named hyperlink in cell a2, a string of my choosing in cell b2, and the formula below in cell C2, Excel crashes if I use the formula below. (I’ve added the module with Jeff’s function in it to the workbook and tried to save it as xlsm before entering the formula, but that doesn’t help).

    =HYPERLINK(URL(a2),b2)

    Ideas?

  12. Emily on September 14, 2022 at 12:54 pm

    Perfect! Exactly what I needed.

  13. Chris W on March 2, 2023 at 1:22 pm

    Really helpful.
    However, if the URL contains a pound sign/hashmark (“#”) the function stops there. The “#” and anything after it is not moved.
    :/

  14. Erik Carlson on March 6, 2023 at 10:21 am

    I am trying to use this to extract a Shared Document link URL, but for some reason it extracts the value with a relative path instead of the absolute path. Example:
    “../../../:b:/s/sbi-test/” so the link is unusable. Anyone solve for this?

  15. Beatrice on May 29, 2023 at 10:42 am

    Hi Jeff,
    Thank you for your video, I’ve used the suggested function but it cuts the URL when in the original hyperlink there is a space. Basically I need to convert the reference to each slide in a presentation (in the hyperlink format) into a URL, and the code only creates the link to the presentation, not to the slide… Any ideas how to solve this?
    Thank you very much and kind regards,
    Beatrice

  16. Bob on June 16, 2023 at 1:02 pm

    This is extremely useful, however I’m running into an issue i’m hoping to get insight on.

    Background:
    I have to enter and alter data in a shared workbook, so I can’t edit at the same time as someone else. We work in different sheets though, so I made all of the cells in my sheet reference a different workbook where I do all my data entry. As a part of our workflow, we right click > edit hyperlink to change the hyperlink address and friendly name of an already existing hyperlink. Some of the link addresses link to a file location on a network drive, while other links link to a website.

    Issue:
    The master spreadsheet that references my separate workbook can successfully “copy” hyperlinks to websites using =HYPERLINK(URL([referebce cell]),[reference cell]) however, when trying to do the same for a file located on a network drive in a subdirectory of the directory where the shared and reference spreadsheet are located, the link address that is pulled by the URL UDF is the original link address, not the one that the link was changed to via “edit hyperlink” option in the right click menu.

    I thought that maybe adding “Application.Volatile” to the UDF might help, but it did not. I’m new to VBA and UDFs so just wondering why the updated hyperlink address is not propogated to the hyperlink.address property when changed via “edit hyperlink” right click menu option… Any ideas?

    • bob on June 19, 2023 at 10:06 am

      disregard. i had made a copy of the master spreadsheet to test out edits in a different directory and that messed with the relative links to the files on the network drive. whoooops.

      • Bob on June 21, 2023 at 1:43 pm

        ACTUALLY the problem is that you cannot indirectly link to other workbooks if the source workbook is closed. If anyone knows a way around this let me know.

  17. Chris Hick on July 26, 2023 at 8:01 am

    Very simple solution – thanks so much

Leave a Comment