# How to extract a URL from a hyperlink on Excel

Today, I faced the problem of extracting URLs from a long list of hyperlinked text. I had two options:

First is to do it manually:

3. Select and copy (Ctrl+C) the entire URL from the Address field of the dialog box.
4. Press Esc to close the Edit Hyperlink dialog box.
5. Paste the URL into any cell desired.

But then I have a long list of hyperlinks in my Excel worksheet. If I had to do this for each and every single hyperlink, this can get tedious very very quickly. So the second option is to get the URLs using a macro.

The following example can be useful when extracting hyperlinks from tables of links that have been copied into Excel, when doing post-processing on Excel web queries, or getting the email address from a list of “mailto:” hyperlinks.

## Extracting a URL from a hyperlink on Excel is easy!

### Option 1: If you want to run this operation one time

1. Open up a new workbook.
2. Get into VBA (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste the Excel user defined function below
5. Press F5 and click “Run”
6. Get out of VBA (Press Alt+Q)

Sub ExtractHL() Dim HL As Hyperlink For Each HL In ActiveSheet.Hyperlinks HL.Range.Offset(0, 1).Value = HL.Address Next End Sub

### Option 2: If you plan to add more hyperlinks to the spreadsheet and need to store the formula on the sheet

1. Open up a new workbook.
2. Get into VBA (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste the Excel user defined function below
5. Get out of VBA (Press Alt+Q)
6. Use this syntax for this custom Excel function: =GetURL(cell,[default_value])

Function GetURL(cell As range, _ Optional default_value As Variant) 'Lists the Hyperlink Address for a Given Cell 'If cell does not contain a hyperlink, return default_value If (cell.range("A1").Hyperlinks.Count <> 1) Then GetURL = default_value Else GetURL = cell.range("A1").Hyperlinks(1).Address & "#" & cell.range("A1").Hyperlinks(1).SubAddress End If End Function

Looking to extract a URL from a hyperlinked image, graphic, or icon? View steps to extract a URL from a hyperlinked image, graphic, or icon »

1. Greg
Posted April 8, 2015 at 4:19 pm | Permalink

Option 1 worked great! What a time saver. Thank you!

• karen
Posted April 8, 2015 at 4:19 pm | Permalink

Glad to hear that it worked for you, Greg

2. Steve
Posted March 16, 2015 at 7:56 am | Permalink

Perfect, exactly what I needed. Thanks!

• karen
Posted April 8, 2015 at 4:19 pm | Permalink

Happy to hear that, Steve!

3. Roshan
Posted March 15, 2015 at 12:53 am | Permalink

Thank you very much. Really helpful this.

• karen
Posted April 8, 2015 at 4:20 pm | Permalink

You’re welcome Roshan!

4. Posted March 12, 2015 at 11:07 am | Permalink

I was trying to extract our 100+ youtube video urls from a list which had text labels and I spent ages going around various sites but your solution works. Thank so much.

• karen
Posted April 8, 2015 at 4:20 pm | Permalink

So glad to hear that this helped you save hours and hours of time! I hate doing things manually

5. Matthew
Posted February 13, 2015 at 12:52 pm | Permalink

Brilliant!

• karen
Posted April 8, 2015 at 4:20 pm | Permalink

You’re welcome Matthew!

6. PRAKASH
Posted February 5, 2015 at 4:44 am | Permalink

THIS POST IS OWSOME.
REALLY SAVE MY ONE DAY.
THANK YOU VERY MUCH

• karen
Posted April 8, 2015 at 4:21 pm | Permalink

You’re welcome Prakash!

7. John
Posted January 22, 2015 at 12:06 am | Permalink

Hi Karen,

Brilliant tip. Thanks for sharing the trick to non-geek guys.

Just in case your readers who come to this article might be looking for solution to extract URLs from a website (other than a link), then this blog post could be useful.

• karen
Posted January 22, 2015 at 4:50 pm | Permalink

Hi John, thanks for sharing this tip! I haven’t tried it yet, but this could definitely be useful!

8. Petter
Posted January 16, 2015 at 6:36 am | Permalink

Great stuff! Worked like a charm and saved me tons of time!

• karen
Posted January 22, 2015 at 4:50 pm | Permalink

9. Güray TONGUÇ
Posted December 12, 2014 at 10:10 am | Permalink

Thank you..

• karen
Posted January 22, 2015 at 4:57 pm | Permalink

You’re welcome!

10. Christina Rumph
Posted November 13, 2014 at 8:35 pm | Permalink

Thank you very very much! I was able to check and confirm correct web links in 5 minutes using your code to get the address. It would have taken me a whole day to do the work manually.

Very much appreciated!!! Cristina

• karen
Posted January 22, 2015 at 5:09 pm | Permalink

Yayy! Thrilled to hear I was able to help you save time, Christina

11. summer
Posted November 5, 2014 at 1:41 pm | Permalink

Thank you very much for saving me time! This was by far the easiest instruction I found on the web to accomplish my goal.

• karen
Posted November 5, 2014 at 1:58 pm | Permalink

You’re welcome, Summer! Glad I was able to help

12. George
Posted October 6, 2014 at 8:45 am | Permalink

Hi,

Thank you for this. It’s exactly what I was looking for.

13. Julius
Posted September 22, 2014 at 6:15 am | Permalink

i want to ask for editing multiple hyperlink, but my hyperink is like this:
Existing:
“C:\pictures\abc.jpg”
“C:\pictures\def.jpg”
“C:\pictures\ghi.jpg”
“C:\pictures\jkl.jpg”

Want edit to:
“C:\My Work\pictures\abc.jpg”
“C:\My Work\\pictures\def.jpg”
“C:\My Work\\pictures\ghi.jpg”
“C:\My Work\\pictures\jkl.jpg”

How to do that ?

thank you

• karen
Posted November 5, 2014 at 2:09 pm | Permalink

Hi Julius, I understand that you want to change the format of your hyperlinks. That’s actually quite easy! You can either use the “Find and replace” function on Excel, or you can use the “SUBSTITUTE” formula. Let me know if I can help!

14. Prince Prabhakar
Posted September 21, 2014 at 7:45 am | Permalink

Hi,

I have a excel sheet exported from a Software where there is hipelinked with file.

for Example Row name is (Ritwik Sengupta.doc) & hiperlink is =”http://zoniac.e-lixirweb.com/Companies/13/Resumes/176/88302/95759/Ritwik Sengupta.doc” HYPERLINK(“Ritwik Sengupta.doc”)

• karen
Posted January 22, 2015 at 5:37 pm | Permalink

Hi Prince, have you already tried the macro above? That should work

15. Alan Lindsay
Posted September 20, 2014 at 1:15 am | Permalink

If your sheet contains both range hyperlinks and shape hyperlinks, Option 1 will fail when it encounters the shape hyperlinks. Using HL.Type to distinguish between the two flavors we can use HL.Shape.TopLeftCell.Offset(0,2) to output shape hyperlinks in the next column after the range hyperlink.

Sub ExtractHL()
If HL.Type = 0 Then
Else
End If

Next
End Sub

16. Posted September 19, 2014 at 9:05 pm | Permalink

Pretty! This has been a really wonderful post.
Thanks for supplying this information.

17. Robyn Farley
Posted August 29, 2014 at 10:59 pm | Permalink

Thank you, so easy, saved my bacon!

18. Posted August 29, 2014 at 12:37 pm | Permalink

Thank you so much for this. I especially appreciate the step-by-step instructions for using VBA as I never have before (successfully). Other tutorials (for various things) always assume you know how to do that part.

Also, this worked like a charm.

19. Posted August 14, 2014 at 6:40 pm | Permalink

Thanks.
It saved me a lot of work!

20. Daragh
Posted August 9, 2014 at 4:21 am | Permalink

Is there any way of extracting the URL from an icon in a cell that is hyperlinked. The GetURL function above works for hyperlinked text. I have a spreadsheet with 3088 hyperlinked icons that I really need to get the URL for each one.

21. Trish
Posted July 30, 2014 at 11:47 am | Permalink

Never usually leave comments as I find that the VBA codes I find never work and after a lot of time and effort I find the answer myself, but I googled, this was the first link and it worked like a charm. Thank you so so much. Made my life so much easier with over 6000 rows of data to analyse!

• karen
Posted July 31, 2014 at 9:29 am | Permalink

Hi Trish! I am so thrilled that this worked for you and that I was able to help you save time

22. Posted July 24, 2014 at 1:58 pm | Permalink

Thank you!

• karen
Posted August 8, 2014 at 9:52 am | Permalink

You’re welcome Praveen

23. Abiola
Posted July 24, 2014 at 2:10 am | Permalink

It works great, thanks man, for this gift

• karen
Posted August 8, 2014 at 9:52 am | Permalink

Hi Abiola! Glad it worked for you

24. Eric Su
Posted June 27, 2014 at 1:48 pm | Permalink

Excellent code! It has saved me a lot of time, and I’ll continue to use it. THANK YOU VERY MUCH!!

25. Michelle
Posted June 24, 2014 at 4:20 pm | Permalink

omg thank you so much. I just needed to do this once, and I’ve never used VBA before, but your post made it so easy! Thank you thank you!

• karen
Posted August 8, 2014 at 9:54 am | Permalink

Yayy! Glad it worked for you, Michelle!

26. Summer Fabian
Posted June 12, 2014 at 2:21 pm | Permalink

Hello!
Can you help me expand this module to include a formula I have to apply to its results now? It would save me time!
After running the module I insert another column and apply this formula:
=RIGHT(B5,LEN(B5)-35)

As you can tell I am trying to extract the tail end of the URL as a text segment.
Thanks!

27. Posted May 27, 2014 at 5:13 pm | Permalink

Hi Karen, I’ve been struggling with this for ages, whenever I use the GetURL function it just shows zero ‘ 0 ‘ in the new resulting cell.. any ideas..? (I’d be happy to make a donation if you can solve this.. it is very frustrating!)

thanks!

• karen
Posted January 22, 2015 at 5:07 pm | Permalink

Hi Leigh, sorry to hear it didn’t work for you! Did you figure out how to make it work? Alternatively, do you posting a link to your spreadsheet so I can see if I can help debug it?

28. Cindy
Posted May 11, 2014 at 12:22 pm | Permalink

Thank you SO MUCH for this clearly written time-saving solution. This is absolutely the prime example of the power of internet sharing!!!

• karen
Posted May 11, 2014 at 5:45 pm | Permalink

You are welcome Cindy!!! So glad I helped you save time

29. Posted April 29, 2014 at 4:29 pm | Permalink

Thank you. The first set of VBA worked exactly as advertised.

• karen
Posted May 1, 2014 at 4:00 pm | Permalink

Glad to hear it worked for you, Mike

30. Paulo
Posted April 5, 2014 at 10:54 pm | Permalink

You’re a genius! Thanks very much

PM

• karen
Posted April 7, 2014 at 4:44 pm | Permalink

31. Bogdan Lalu
Posted April 4, 2014 at 6:34 pm | Permalink

Thank you very much, this was so useful !

32. Posted March 27, 2014 at 2:17 pm | Permalink

Brilliant! Thank you very much for posting this!

• karen
Posted April 7, 2014 at 4:43 pm | Permalink

You’re welcome Kirby!

33. Wu
Posted March 12, 2014 at 1:27 pm | Permalink

Sir, you have saved me a ton of time with this. Thank you.

• karen
Posted March 12, 2014 at 1:31 pm | Permalink

Happy to hear that I saved you time, Wu!

• Don
Posted March 21, 2014 at 12:57 pm | Permalink

I’m getting Run-time error ‘1004″:
Application-defined or object-defined error

I’m not programming savvy, so any help is appreciated.

34. imran
Posted March 8, 2014 at 1:11 pm | Permalink

Awesome. Works exactly as written. Thank you!

• karen
Posted March 12, 2014 at 1:31 pm | Permalink

That’s great to hear Imran

35. Posted February 14, 2014 at 4:58 pm | Permalink

Simple and fantastic ! Thanks a lot !!

• karen
Posted March 12, 2014 at 1:32 pm | Permalink

You’re welcome Richard!

36. Jeremiah
Posted December 12, 2013 at 10:22 am | Permalink

Thanks Karen, but that is not quite what I was looking for. I want it done automatically within a function (like the one above). I have web page report that I copy/paste onto a tabbed spreadsheet within a workbook. . That report contains the numbers with the underlying hyperlinks. I have another tabbed spreadsheet on that workbook that displays bit and pieces of the various data that I just pasted into the workbook. Your “Get URL” function works well, but it displays the whole web address. I want it to display the number with the underlying hyperlink, so my users can click on the number and it will take them to the corresponding webpage to display the raw data. I hope I am explaining this better.

• karen
Posted December 12, 2013 at 1:11 pm | Permalink

Hi Jeremiah, I apologize for misunderstanding your earlier comment. To make sure that I understand you, do you mind uploading a dropbox link to the Excel file so I can see exactly what you mean? I’d be happy to have a look and see how I can help.

37. Jeremiah
Posted December 8, 2013 at 1:00 pm | Permalink

Love the solve, but I do have a question. I want to copy a cell that contains a number with a underlying hyperlink to a webpage that gives the raw data that makes up that corresponding number. When I use your “GET URL” function it returns the web address. How do I copy the number and the underlying hyperlink to another cell?

• karen
Posted December 9, 2013 at 12:05 pm | Permalink

Hi Jeremiah, you can just highlight the original column with the number with the underlying hyperlink, copy the content, and paste them as values onto another column. That strips out the hyperlinks.

• Jeremiah
Posted December 10, 2013 at 9:29 am | Permalink

Thanks Karen, but that is not quite what I was looking for. I want it done automatically within a function (like the one above). I have web page report that I copy/paste onto a tabbed spreadsheet within a workbook. . That report contains the numbers with the underlying hyperlinks. I have another tabbed spreadsheet on that workbook that displays bit and pieces of the various data that I just pasted into the workbook. Your “Get URL” function works well, but it displays the whole web address. I want it to display the number with the underlying hyperlink, so my users can click on the number and it will take them to the corresponding webpage to display the raw data. I hope I am explaining this better.

• karen
Posted December 12, 2013 at 1:11 pm | Permalink

Hi Jeremiah, I apologize for misunderstanding your earlier comment. To make sure that I understand you, do you mind uploading a dropbox link to the Excel file so I can see exactly what you mean? I’d be happy to have a look and see how I can help.

38. Jeremiah
Posted December 6, 2013 at 4:09 pm | Permalink

Karen, what a great solve, but I do have a question. My hyper links are represented by a number. When you click on that number it takes you to a corresponding web page and gives you the data that make up that number. The solve you give here extracts the link and pastes it to another cell. How do I retain the number and the underlying hyperlink?

• karen
Posted December 9, 2013 at 12:05 pm | Permalink

Hi Jeremiah, you can just highlight the original column with the number with the underlying hyperlink, copy the content, and paste them as values onto another column. That strips out the hyperlinks.

39. Harry
Posted December 5, 2013 at 1:01 pm | Permalink

Well my problem is fixed. I found someone named Telegraf had found a solution. The part of the link after the # is regarded as a subaddress, so the whole thing can be got with
with no line break of course.

Mental health no longer under threat. For the moment

• karen
Posted December 9, 2013 at 12:16 pm | Permalink

Hi Harry! I am so glad that you found a solution that worked. Thank you for posting it here! I’ve also updated my post above so that other folks facing the same issue as you can make use of what you’ve learned. Thanks Harry!

40. Harry
Posted December 5, 2013 at 12:41 pm | Permalink

More info. If I insert a hyperlink like that but without the hash (pound) symbol the function returns the whole correct link. With the hash it only returns the link up to but excluding the hash. The Edit Hyperlink dialog always returns the whole thing. ???

41. Harry
Posted December 5, 2013 at 11:09 am | Permalink

The links in my sheets are of the form “http://fruit.com/bananas/#123456″
The function only extracts from that “http://fruit.com/bananas/” and misses the rest out. If I use the Excel Edit Hyperlink dialog it shows the full thing, so I know it’s there. Any ideas as to how to extract the full thing in code?
Thanks in advance, I’m going mental here

42. Posted December 3, 2013 at 3:28 pm | Permalink

Gah! This just totally saved me. Thank you, thank you!

I was copying a large formatted table with hyperlinks from a website, and needed to extra the links. After I pasted into Excel and used this function, I have exactly what I needed.

• karen
Posted December 4, 2013 at 9:34 am | Permalink

Hi Jenna! I am so glad that I saved you some time

43. Doug
Posted November 26, 2013 at 3:10 pm | Permalink

Works perfectly. I love it when Google finds me such a clean solution in less time than writing one myself.

• karen
Posted December 4, 2013 at 9:33 am | Permalink

Happy it worked well for you, Doug!

44. Sandeep
Posted November 25, 2013 at 5:16 pm | Permalink

Wow! Thank you so much!

• karen
Posted December 4, 2013 at 9:33 am | Permalink

You’re so welcome, Sandeep!

45. mayl
Posted November 21, 2013 at 5:01 pm | Permalink

Does this work for a iMac as well? I’m trying it on my iMac and it doesn’t seem to be working (specifically the “Alt-F11″)?…

• karen
Posted December 4, 2013 at 9:33 am | Permalink

Hi Mayl! You’re right, the ALT-F11 shortcut only works on a PC. If you’re on a Mac, use Alt-Fn-F11.

46. Simon Grey
Posted November 20, 2013 at 7:32 am | Permalink

Great function, but I can only get it work with one cell at a time. If I try to drag the formula, it won’t work on the range of cells selected. Anyone else had this problem?

• karen
Posted November 20, 2013 at 7:38 am | Permalink

Hi Simon! I am sorry to hear that it didn’t work for you when you tried to drag down the formula. I’ve used this a number of times and it has always worked for me. One thing that you can try is to change the cell range from “A1″ to, say, “A:A”. Perhaps that might work?

47. Posted November 15, 2013 at 1:49 am | Permalink

Really works for me. I am very happy with this Module. Save a lot of mine time.

thanks again

• karen
Posted November 20, 2013 at 7:34 am | Permalink

So happy to hear that it saved you a lot of time

48. Posted November 9, 2013 at 2:28 pm | Permalink

Thanks a LOT! I used VBA for the first time and it worked! Had to do it in a new tab instead of a new file though.

• karen
Posted November 20, 2013 at 7:34 am | Permalink

Hi Zuhaib! Glad to hear that it worked well for you even though it’s the first time you’ve used this!

49. Tony
Posted November 1, 2013 at 10:46 am | Permalink

I’m glad that your method has worked so well for many people, but this is what worked for me:

Create a macro:

Sub ExtractHL()
Next
End Sub

Run that macro.
Done.

• karen
Posted December 19, 2013 at 12:21 pm | Permalink

Hi Tony! Thanks for sharing this alternative macro. This macro does look a lot simpler than the one I originally suggested above. However, the big disadvantage of this macro is that you have to run it every time you want to perform the operation. But for most uses, that works perfectly fine! I’ve updated my blog post above and included your suggested macro. Thanks again so much for sharing this!

50. Tony
Posted November 1, 2013 at 10:34 am | Permalink

I must be missing something, because people are saying it’s saved them so much time, but I’m finding I have to enter the formula and then tell it which cell it’s getting the url from, for each and every URL. That’s faster than the manual method of editing the hyperlink, copying it and pasting it, but it’s not a whole lot faster–I’ll still be days, maybe weeks editing thousands of lines of formula… Any hints here?

(Also, if I don’t put [default_value] in quotes, it throws an error.)
(Finally, this works to display the URL, but it’s not a clickable hyperlink…)

• karen
Posted November 1, 2013 at 12:30 pm | Permalink

Hi Tony! You can easily just drag the formula all the way down. Have you tried that?

• Kaushlya
Posted November 19, 2013 at 12:42 am | Permalink

thanks a lot!!!
it solved my problem….

• karen
Posted November 20, 2013 at 7:34 am | Permalink

Hi Kaushiya! Happy to hear it solved your problem

51. Amanda
Posted October 31, 2013 at 12:24 pm | Permalink

I must be missing something because I followed the instructions but can’t seem to get anything to happen. I did it in a new workbook and a tab in the same workbook but nothing. Where do you enter the new formula? Do I enter a cell range?

• karen
Posted October 31, 2013 at 1:54 pm | Permalink

Hi Amanda! I am sorry to hear that it did not work for you! I understand that you added a second worksheet in the same Excel file and added the formula in the second worksheet. Is that correct? If so, just make sure that you reference the correct “cell” in the original worksheet when you use this formula: =GetURL(cell,[default_value]). Hope this helps, Amanda!

• Amanda
Posted October 31, 2013 at 3:11 pm | Permalink

Still can’t do it.
I copied and pasted exactly what you had in the exact format that you had it. Maybe it’s not supposed to be on multiple lines?

And where would you enter the formula? I copied and pasted it exactly as you had it as well. Nothing happens. So I’m missing something for sure. At this point maybe it would have been faster to just open them one by one!

52. Guillermo
Posted October 30, 2013 at 5:44 pm | Permalink

I love you Karen! took me less than a minute keep up the brilliant stuff

• karen
Posted October 30, 2013 at 6:02 pm | Permalink

You are very welcome, Guillermo! I’m just so happy to see that I was able to help

53. Antony
Posted October 10, 2013 at 5:40 pm | Permalink

Hi Karen,
When I run this, return:
B1 = javascript:
B2 = javascript:void(0);
B3 = javascript:void(0);
B… = javascript:void(0);

User: Win7 – IE9 – Excel 2010 (macros are enabled) – Java (version 7 (build 1.7.0_40-b43))

• karen
Posted October 10, 2013 at 6:46 pm | Permalink

Hi Antony! I suspect that there’s something wrong with your URLs. Can you try to extract the URLs manually to confirm my suspicion? Just follow these steps:

3. Check the entire URL from the Address field of the dialog box. If it shows “javascript:void(0)”, then the problem is indeed with the URLs.

54. Daniel
Posted October 4, 2013 at 11:00 am | Permalink

That was brilliant! I had tried other VBA solutions, none of which worked.
Thanks so much Karen!

• karen
Posted October 4, 2013 at 11:30 am | Permalink

I am so happy that worked for you, Daniel! That’s great to hear

55. Posted September 27, 2013 at 1:10 pm | Permalink

Thanks a loooot karen..
you took me to cloud nine buddy..
kindly share or mail me, if you have other similar projects which might help people like me over here…

Thanks a lot buddy..

• karen
Posted September 27, 2013 at 1:31 pm | Permalink

Dwarkesh, you’re very welcome! I’m just so happy to hear that I was able to help you

56. vivek
Posted September 20, 2013 at 8:28 am | Permalink

Superlike, Save a lot

Thanks for sharing such a use full function, But I have one challenge for you how can we get anchor text from a url.

Say for an example I have content which posted on blog sites now I want to fetch those anchor text which was given in a content.

Do you have any idea?

Thanks for this Post
Vivek

• karen
Posted September 27, 2013 at 11:15 am | Permalink

You’re welcome, Vivek! I understand that yo want to extract the anchor text from a URL. This should be easy. Just go ahead and copy all of them and paste them as values.

57. Posted September 4, 2013 at 3:49 pm | Permalink

Thanks!

• karen
Posted September 4, 2013 at 5:01 pm | Permalink

You’re welcome Andrew!

58. David
Posted August 30, 2013 at 12:26 pm | Permalink

For those of you getting an error in the formula. Try opening a new TAB (worksheet) not a new FILE (workbook).

Worked like a charm after I did the VBA module in the SAME FILE.

Thanks Karen!

• karen
Posted August 31, 2013 at 6:19 am | Permalink

Thank you for the tip, David!

59. BB
Posted August 19, 2013 at 5:15 pm | Permalink

Anyway to modify this script to extract a url from a hyperlinked image? Thanks!

• karen
Posted August 19, 2013 at 6:52 pm | Permalink

Hi BB! Unfortunately, I don’t know of an Excel formula that can extract the URL from a hyperlinked image. Sorry about that! But perhaps there’s a macro that can do this?

60. Chris Taylor
Posted August 16, 2013 at 11:05 am | Permalink

Thanks hugely for sharing this — saved my sanity.

• karen
Posted October 1, 2013 at 9:05 am | Permalink

You’re welcome, Chris

61. Posted August 8, 2013 at 10:47 pm | Permalink

OMG!!!! thank you so much. I can’t believe how fast that was for 1700+ lines.

• karen
Posted August 9, 2013 at 9:11 am | Permalink

You’re welcome, Shelly! So happy I was able to help you save a lot of time!

62. Carlos Alves
Posted July 29, 2013 at 2:32 pm | Permalink

Hello Karen!

Nice post, this macro will be very useful. I wonder if there is the posibility of testing those links with a macro. The function would return me as valid or invalid link. Do you know if something like that is possible with excel?

Thank you!

63. Gigi
Posted July 25, 2013 at 12:32 pm | Permalink

Thank you very much. Brilliant!

• karen
Posted August 7, 2013 at 5:29 pm | Permalink

You’re welcome, Gigi! Glad I was able to help!

64. joe ferraro
Posted July 2, 2013 at 7:12 am | Permalink

Nice – Quick – Accurate – can’t ask for more

THANKS!!!

• karen
Posted August 7, 2013 at 5:29 pm | Permalink

You’re welcome Joe! Glad I was able to help

65. CJ
Posted June 27, 2013 at 5:09 pm | Permalink

oh, got it,
works to pull out the anchor link. Brilliant. Thanks again.

• karen
Posted June 27, 2013 at 5:17 pm | Permalink

Hi CJ! So glad you figured out a way to edit the macro so that it works for you! I can’t imagine having to manually extract these links from hundreds of worksheets, so I am happy it worked out

66. CJ
Posted June 27, 2013 at 5:00 pm | Permalink

Thanks for this Karen. It works for hyperlinks starting with http://www…. but do you know how I can extract hyperlinks which are local links to other worksheets?

They are stored as anchors e.g. #nameofworksheet!A1

Any idea how I can strip out these links? I have 300+ rows where someone has created 200+worksheets in a workbook (can you imagine!) and I need to extract data from ‘from these links’ so if I can get the anchor text out, I can just create a simple formula to concatenate and pull out the cell references. Any ideas? Thanks

67. Dave
Posted June 27, 2013 at 11:28 am | Permalink

Never mind – I FIXED IT! silly error.
Thanks, this works great!

• karen
Posted June 27, 2013 at 1:12 pm | Permalink

Happy to hear that it finally worked, Dave

68. Dave
Posted June 27, 2013 at 11:22 am | Permalink

When I run this I get a VB error:
Ambiguous name detected: GetURL

~ what am I missing?

69. Posted June 21, 2013 at 8:12 am | Permalink

Wow, thank Karen – that just saved me HEAPS of time

Just one thing, I opened a new workbook as you said, but when I went back to the workbook with all my links in, the function didn’t work – I got #NAME? like David. So I went back into VBA and realised the function was associated with the new workbook and not my workbook with the links in. When I did it again for the right workbook it worked like a dream!

This maybe the problem the problem David was having.
Thanks again!

• karen
Posted June 21, 2013 at 10:33 am | Permalink

That may indeed be the same problem that Scott was facing! Thanks for sharing this tip, Wendy!

70. David
Posted June 19, 2013 at 3:10 am | Permalink

copy and paste it WHERE???? Not as easy as it sounds?

• karen
Posted June 20, 2013 at 10:50 am | Permalink

Hi David,

You first have to do the ff:

1. Open up a new workbook.
2. Get into VBA (Press Alt+F11)
3. Insert a new module (Insert > Module)

After this step, you’ll see a window. That’s where you should copy and paste the Excel user defined function above.

Let me know if it still doesn’t work for you, David. I’ll be here to help!

Karen

71. Bev
Posted June 17, 2013 at 10:42 pm | Permalink

Thank you, thank you. This is great.

72. Alison
Posted June 7, 2013 at 4:17 am | Permalink

Thanks a bunch, Karen! First useless thing at new internship (Check)

Really. Saved a lot of time!

• karen
Posted June 10, 2013 at 8:46 pm | Permalink

You’re welcome, Alison I hope you impressed your bosses there by finishing the task so quickly!

73. Matt
Posted June 6, 2013 at 7:31 pm | Permalink

Thank you! Saved me hours of work!!

• karen
Posted June 10, 2013 at 8:47 pm | Permalink

You’re welcome Matt! Happy I was able to save you hours of work

74. Posted May 22, 2013 at 11:34 am | Permalink

I get a #NAME? error.

Should the Function be in a module in VBAProject (PERSONAL.XLSB) ?

I have a list of hyperlinks that I want to find using VLOOKUP to find the friendly name and retrieve the entire hyperlink, e.g.

is in sheet 2 and I want to lookup using US5411693A as the lookup value in sheet 1.

I can use that and get the match from sheet 2, but only the friendly name is returned.

Thanks,

Scott

• karen
Posted June 20, 2013 at 12:00 pm | Permalink

Hi Scott, a #NAME? error usually occurs when Microsoft Excel does not recognize text in a formula. For example, you may have entered text in a formula without enclosing it in double quotation marks. When I look at your HYPERLINK formula above, I see that you have used the curly quotation marks (“) instead of the straight quotation marks (“). I suspect that’s the cause of this #NAME? error. Can you try replacing these quotation marks with straight quotation marks and let me know if that gets rid of the error?

75. Posted May 16, 2013 at 4:13 pm | Permalink

Thanks for this

• karen
Posted May 16, 2013 at 4:52 pm | Permalink

You’re very welcome, Brian

76. VG
Posted April 29, 2013 at 9:46 am | Permalink

Thank you, your post saved almost half a day for me

• karen
Posted May 14, 2013 at 2:40 pm | Permalink

So happy that my post helped you save so much time

77. oscar
Posted April 25, 2013 at 6:38 pm | Permalink

Fantastic! now imagine if we could sum up all the hours that you saved from everyone I’d guess a few thousands hrs.

Thanks a bunch
OG

• karen
Posted April 25, 2013 at 7:33 pm | Permalink

Oscar, I’m so happy to hear that it saved you a lot of time

78. Posted April 20, 2013 at 5:02 am | Permalink

• karen
Posted April 22, 2013 at 12:56 pm | Permalink

Jagan, sorry to hear that it was not working for you. I believe it does work for Office 2010 too. Could it be that your macros are just disabled? At which step is it failing?

79. RohitSharmaCREATRIX
Posted April 16, 2013 at 3:59 am | Permalink

Great Code… Worked Well, and saved huge time….

Cheers..

• karen
Posted April 22, 2013 at 5:47 pm | Permalink

80. khan
Posted April 4, 2013 at 12:04 am | Permalink

awesome..

• karen
Posted April 4, 2013 at 12:06 am | Permalink

81. taylor
Posted April 3, 2013 at 10:34 am | Permalink

I’m getting everything to work Karen but for some reason the cell says mailto:(person’semailaddress) but when I click it to try and copy and past it just says the formula again. What am i doing wrong? I just want it to read as the person’s email address it would save me hours of time.

• karen
Posted April 3, 2013 at 3:50 pm | Permalink

Oh that’s easy to do, Taylor. Just follow these steps:

1. Highlight the entire column containing the formulas.

2. Press CTRL-C on your keyboard to copy the content of all the cells in that column.

3. Right-click and select “Paste Special…”

4. Select “Values”.

6. Click the “OK” button.

7. Press CTRL-F to launch the “Find” popup.

8. Enter “mailto:” next to “Find what:”.

9. Click the “Replace” tab.

10. Click the “Replace All” button.

Hope this helps!

82. Paul Miller
Posted March 20, 2013 at 4:47 pm | Permalink

This ROCKS!! Saved me some tedious work! THANK YOU!!

• karen
Posted March 20, 2013 at 8:07 pm | Permalink

Paul, it makes me so happy to hear that I’ve saved you time Glad to hear this post helped!

83. Bixplat
Posted March 19, 2013 at 9:04 am | Permalink

I think where some people are getting confused is with the [default_value] portion. Here is an example of what will work. Data is in Row 1 Column A. Click on Row 1, Column B. Use =GetURL(A1,) . This should work.

84. Jason
Posted March 18, 2013 at 3:12 am | Permalink

How can I save it permanently in excel? Because everytime I open the excel this function is gone

• karen
Posted April 22, 2013 at 5:50 pm | Permalink

Jason, if you are using the same Excel file, it should remain there. However, if you are creating a new file, you have to re-insert the macro.

85. effectsalive
Posted February 14, 2013 at 6:52 pm | Permalink

Making use of =hyperlink formula with text and multiple cells. At first this did not work, but copy and paste into word then back to excel and now I got all the full URLs!

• karen
Posted February 15, 2013 at 9:31 am | Permalink

Thank you for your suggestion! I have not tried that function as a way to extract URLs, but I’m happy to hear that it worked for you

86. Brian
Posted February 13, 2013 at 11:37 am | Permalink

Okay, I understand the macro part but what is step 6 referring to with “Use this syntax for this custom Excel function: =GetURL(cell,[default_value])”

What is a syntax, where do I copy and paste “=GetURL(cell,[default_value])”

If helps, the hyperlinks I’m trying to convert are in cells ranging from G18 to G50.

• karen
Posted February 15, 2013 at 9:36 am | Permalink

Brian, you can just use the function “=GetURL(cell,[default_value])” on your Excel sheet as you would any other standard Excel formula. For example, if cell A1 contains the hyperlinked text whose URL who you want to extract, you can just type “=GetURL(A1)” on any other cell on the sheet to extract the hyperlink. Let me know if I can answer any other questions! I’ll be happy to help.

87. Lowrance
Posted February 1, 2013 at 2:28 am | Permalink

Wow it worked even in 2013 version file…Thank you very much

• karen
Posted February 1, 2013 at 8:35 am | Permalink

So happy it worked for you, Lowrance!

88. Jake
Posted January 27, 2013 at 10:54 am | Permalink

Thanks!!!

• karen
Posted January 27, 2013 at 11:13 am | Permalink

You’re welcome, Jake!

89. Posted January 21, 2013 at 11:22 am | Permalink

it’s worked, it worked!!! thanks!!!!!

• karen
Posted January 21, 2013 at 7:02 pm | Permalink

Happy to hear that it worked for you!

90. Chevy
Posted December 20, 2012 at 3:47 pm | Permalink

Thank you VERY much!

• karen
Posted January 10, 2013 at 6:27 pm | Permalink

You’re welcome, Chevy! Glad I was able to help

91. Posted December 13, 2012 at 8:02 pm | Permalink

I’m trying to extract email addresses from hyperlinked picture objects (email symbols) which I have placed in an Excel file. I followed your steps. But it is not doing the job. Any suggestions? Best regards, Donnell

• karen
Posted December 13, 2012 at 9:02 pm | Permalink

Hi Donnell! I have not tried this method for hyperlinked pictures, but I suspect that it won’t work because pictures are not really within cells. The only thing I can think of is doing it manually, but that’s really tedious. If there’s no way to do it except manually, consider hiring someone on Odesk.com to do it for you for a few bucks instead I am sorry that I couldn’t be of more help!

92. Steve Loos
Posted October 18, 2012 at 1:56 pm | Permalink

Thank you for posting this! However I followed your steps. I am familiar with excel functions.
Your code assumes the email “mailto” url is in cell A1, correct? This is where I have hyperling “john smith” which is “mailto@johnsmit@mail.com”
I put the =GetURL(cell,[default_value]) into cel B1 but nothing happens, or I get an error.
I am sure I am close!
Steve; excel 2010.

• karen
Posted October 18, 2012 at 2:06 pm | Permalink

Hi Steve! You are indeed very close! Just put =GetURL(A1) and that should work. If you have an entire column of URLs, you can just drag that formula all the way down. Let me know in case it doesn’t work. Good luck!

93. Lacoste
Posted October 6, 2012 at 2:46 am | Permalink

Morning, I thought the whole day going to the 4700 lines, but now half a minute to complete. Thank you very much!

• karen
Posted October 6, 2012 at 10:35 am | Permalink

Glad to hear this saved you time!

94. Jeff
Posted October 4, 2012 at 10:13 am | Permalink

Hi Karen,
Seems like this could save me a ton of time, however I keep getting an error message.
Operating in Excel 2007
When I hit enter I go immediately back to MVB
Pop Up Compile error: Invalid outside procedure
Book 2 – Sheet 2 (Code)
Set rng = rng(1)

GetURL = “B”
Else
End If
End Function

Behind that in another box is
Book 2 – Module1 (Code)
Function GetURL(cell As Range, _
Optional default_value As Variant)
‘If cell does not contain a hyperlink, return default_value
GetURL = default_value
Else
End If
End Function

I do not have super strong excel skills but follow directions well. Please help me if you can. Thank you in advance, I am certain I am doing something small wrong
-Jeff

• karen
Posted October 4, 2012 at 10:44 am | Permalink

Jeff, I’m sorry to hear that you are getting these errors. You mentioned that you are seeing the error when you hit “enter”. Can you clarify on which step you are hitting “enter”? I don’t think there’s a step that requires you to do that. I Did you try these steps below? Can you try these again?

1. Open up a new workbook.
2. Get into VBA (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste the Excel user defined function above.
5. Get out of VBA (Press Alt+Q)
6. Use this syntax for this custom Excel function: =GetURL(cell,[default_value])

95. Posted September 13, 2012 at 7:03 am | Permalink

If you were in the same room as me right now I would hug you. Life saver!

• karen
Posted September 13, 2012 at 7:42 am | Permalink

So happy I could help

96. Sarah
Posted September 4, 2012 at 3:22 am | Permalink

You are a life saver! that’s 4000+ lines worth of work.. thanks!

97. Jesse
Posted July 2, 2012 at 1:31 pm | Permalink

Ummmm, these are horrible instructions for a total newb like me. How do I apply this to all of my cells with anchor-texted-hyperlinks in them?

• karen
Posted December 13, 2012 at 8:58 pm | Permalink

I am sorry to hear that they didn’t work? Did you try these steps below? It should be pretty simple.

1. Open up a new workbook.
2. Get into VBA (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste the Excel user defined function above.
5. Get out of VBA (Press Alt+Q)
6. Use this syntax for this custom Excel function: =GetURL(cell,[default_value])

98. Bruno
Posted June 17, 2012 at 8:52 am | Permalink

Good stuff. Big thanks to Karen.

99. dhiraj das
Posted May 14, 2012 at 3:01 am | Permalink

Thanks a lot!! Could save my time.

100. covaz
Posted January 25, 2012 at 2:21 pm | Permalink

Thank you!!! You just saved me about 30 mins of work!

• karen
Posted January 25, 2012 at 3:26 pm | Permalink

You are welcome! I am happy that my post saved you a lot of time!