Get the URL from a hyperlink in Excel
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
February 15th, 2012 at 10:48 am
EXTREMELY helpful. Made my day!
February 16th, 2012 at 1:25 pm
Awesome solution. Thank you. Thank you. Thank you.
February 23rd, 2012 at 2:39 pm
Quick and easy! Thanks!
February 27th, 2012 at 1:38 am
very helpful, thanks
April 5th, 2012 at 1:51 am
can u show me this with example of excel
April 6th, 2012 at 12:22 am
I’ve posted a video of how to do this on the blog.
April 22nd, 2012 at 5:17 am
Genius…. 🙂
May 15th, 2012 at 4:34 pm
Excellent Solution ..You really are awesome,,,
June 5th, 2012 at 3:15 am
I also have to thank you for this macro & solution in order to retrieve the url from hyperlinks.
Is there a possibilty to create a macro that gives you the name of the website out of the hyperlink?
It is the name that you can see when you hit the hyperlink with the cursor.
I’m looking forward to find this.
June 5th, 2012 at 3:35 am
Hello everyone,
I found allready the answer/solution to my question.
If anyone is interested in applying it, you only have to create another module with the same content as the previous one, but replace “Address” with “Screentip”. It’s as simple as that.
June 5th, 2012 at 7:12 am
Jan Thank You for the solution…
June 13th, 2012 at 7:45 am
So i did everything you said and i got what the the underlying hyperlink said, mine being an email address. However i cant copy all of them or edit them in any way is there any way to do that?
June 19th, 2012 at 10:30 am
Brilliant sir…if u have any id about that please share my email address.
June 22nd, 2012 at 10:07 am
That is awesome! Thanks-solved my problem in 45 seconds!
August 2nd, 2012 at 11:05 am
Thanks!
August 11th, 2012 at 3:29 pm
Thank you! Genius 🙂
October 19th, 2012 at 9:47 am
This is great!! I’ve looked for a solution to this problem off and on for a few years, and solutions have always been complex with inconsistent results. This is super easy to use and works perfectly. Thanks so much!!
October 21st, 2012 at 9:32 pm
Thanks Bohack. I love the background music. Couldn’t make out the song though. May I ask what it is ?
Peace.
October 21st, 2012 at 9:39 pm
Thanx… The background music is Dexter Britain – The Lost Ones.
November 20th, 2012 at 5:46 am
Thanx
January 1st, 2013 at 5:07 am
Thanks.. Helped a Lot….
January 1st, 2013 at 3:48 pm
Thanks for providing such a perfect solution, with concise and clear instructions for getting this to work!
April 10th, 2013 at 12:19 pm
THANKS!