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
A most excellent solution. Thank you very much.
excelent !
Brilliant, thanks for sharing.
Thanks for KISS (keep it simple & smart) example!
Cheers !
Helped me, thanks for sharing.
Was very helpful and time-effective. Thanks
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… ๐
Beautiful!
Thanks so much for this. I wish I would have searched for it yesterday, but today it saved me a lot of time.
This was working yesterday but today all i get is #NAME?… Any advice?
Sounds like the macro is no longer there… You probably have to create the macro again, not sure why…
Even I get the same error, #NAME? Just wondering if it’s because of Office 2007
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.
Superd…. thanks
thanks .. my problem has been solved. .
nice solution
This is an awesome little macro. Thanks for the tip!
Awesome – thanks!
Unfortunately, this only works for web addresses. Is there a way to extract a path on a local computer from the hyperlink? Thanks.
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.
Thanks for sharing.
G R E A T !
Thank you!
thanks .. my problem has been solved. .
nice solution Superdโฆ. thanks
EXTREMELY helpful. Made my day!
Awesome solution. Thank you. Thank you. Thank you.
Quick and easy! Thanks!
very helpful, thanks
can u show me this with example of excel
I’ve posted a video of how to do this on the blog.
Genius…. ๐
Excellent Solution ..You really are awesome,,,
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.
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.
Jan Thank You for the solution…
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?
Brilliant sir…if u have any id about that please share my email address.
That is awesome! Thanks-solved my problem in 45 seconds!
Thanks!
Thank you! Genius ๐
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!!
Thanks Bohack. I love the background music. Couldn’t make out the song though. May I ask what it is ?
Peace.
Thanx… The background music is Dexter Britain – The Lost Ones.
Thanx
Thanks.. Helped a Lot….
Thanks for providing such a perfect solution, with concise and clear instructions for getting this to work!
THANKS!