Get the URL from a hyperlink in Excel
Print This Post
Recently I needed to extract a links from a web page; normally I would use some Linux commands ported to windows and get the job done. In the end I needed to format the relative link to a full URL, so I decided to use Excel. However when I imported the web page into excel I found that the URL was embedded in the friendly name of the hyperlink.
The hyperlink command in excel actually does the reverse of what I needed; it creates a hyperlink out of the URL and a friendly name and I needed the URL from the friendly name. So I dug in a little deeper and treated the URL like an object. It has properties and those properties can be exposed. The hyperlinkcell object has a type of range so it is passed to the function of GETURL. The first hyperlink in the range we will want the property of the address. Then we simply pass it back to the function so that we can display it.
To install this all you need to do is:
- Hit ALT + F11 (Opens Visual Basic Editor)
- Click on Insert -> Module (adds a module to your excel file)
- Paste the code below for the function of GETURL
- Hit ALT + Q (Closes the Visual Basic Editor)
Now use the =GETURL(cell) to get the URL
Example: =GETURL(A1) will return the URL for the Hyperlink displayed in cell A1
Function GETURL(HyperlinkCell As Range) GETURL = HyperlinkCell.Hyperlinks(1).Address End Function
October 9th, 2009 at 1:19 pm
A most excellent solution. Thank you very much.
May 15th, 2010 at 11:21 am
excelent !
February 9th, 2011 at 10:59 am
Brilliant, thanks for sharing.
March 8th, 2011 at 3:36 pm
Thanks for KISS (keep it simple & smart) example!
Cheers !
March 25th, 2011 at 3:11 am
Helped me, thanks for sharing.
June 3rd, 2011 at 6:47 am
Was very helpful and time-effective. Thanks
June 8th, 2011 at 3:58 am
Thanks a lot. I wasn’t able to import hyperlinks from excel to access till now . I wonder why MS don’t have a function doing this…
June 9th, 2011 at 1:58 pm
Beautiful!
July 1st, 2011 at 1:01 pm
Thanks so much for this. I wish I would have searched for it yesterday, but today it saved me a lot of time.
July 20th, 2011 at 4:15 am
This was working yesterday but today all i get is #NAME?… Any advice?
July 20th, 2011 at 6:16 pm
Sounds like the macro is no longer there… You probably have to create the macro again, not sure why…
July 26th, 2011 at 5:55 am
Even I get the same error, #NAME? Just wondering if it’s because of Office 2007
July 26th, 2011 at 3:38 pm
Starting with Excel 2007 Macros are disabled by default. If you create the macro it will be enabled until it is saved for the first time. It will also need to be saved as an XLSM (macro enabled) Excel document. The next time it is opened a dialog box will ask you to enable the macro. The dialog box is under the formula bar of the spreadsheet. Hope that helps, but that is the behavior when saving the excel and macro.
September 15th, 2011 at 12:06 am
Superd…. thanks
December 13th, 2011 at 9:00 am
thanks .. my problem has been solved. .
nice solution
December 14th, 2011 at 7:15 pm
This is an awesome little macro. Thanks for the tip!
January 5th, 2012 at 6:42 am
Awesome – thanks!
January 13th, 2012 at 4:35 pm
Unfortunately, this only works for web addresses. Is there a way to extract a path on a local computer from the hyperlink? Thanks.
January 16th, 2012 at 4:54 pm
I just tested the code and it seems to work on the URLs as well. Since the hyperlink is just a link redirecting either the web browser or explorer shell to another location.
January 27th, 2012 at 8:09 am
Thanks for sharing.
January 28th, 2012 at 11:03 am
G R E A T !
Thank you!
February 3rd, 2012 at 12:23 am
thanks .. my problem has been solved. .
nice solution Superd…. thanks