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:

  1. Right-click a hyperlink.
  2. From the Context menu, choose Edit Hyperlink. Excel displays the Edit Hyperlink dialog box.
  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

This entry was posted in Macros. Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

141 Comments

  1. 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 :)

  2. 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 :)

  3. 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

      Paulo, you are so welcome! So glad I was able to help you!

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

    Thank you very much, this was so useful !

  5. 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!

  6. 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!

  7. 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 :)

  8. 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! :)

  9. 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.

  10. 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.

  11. 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.

  12. 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
    GetURL = cell.range(“A1″).Hyperlinks(1).Address & “#” & cell.range(“A1″).Hyperlinks(1).SubAddress
    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!

  13. 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. ???

  14. 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 :(

  15. 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 :)

  16. 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!

  17. 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!

  18. 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.

  19. 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?

  20. 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 :)

  21. 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!

  22. 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:

    http://excel.tips.net/T003281_Extracting_URLs_from_Hyperlinks.html

    Create a macro:

    Sub ExtractHL()
    Dim HL As Hyperlink
    For Each HL In ActiveSheet.Hyperlinks
    HL.Range.Offset(0, 1).Value = HL.Address
    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!

  23. 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 :)

  24. 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!

  25. 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 :)

  26. 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))

    Please help. Thanks!!!

    • 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:

      1. Right-click a hyperlink.
      2. From the Context menu, choose Edit Hyperlink. Excel displays the Edit Hyperlink dialog box.
      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.

  27. 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 :)

  28. 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 :)

  29. 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.

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

    Thanks!

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

      You’re welcome Andrew!

  31. 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! :)

  32. 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?

  33. 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 :)

  34. 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! :)

  35. 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!

  36. 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!

  37. 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 :)

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

    oh, got it,
    GETURL = HyperlinkCell.Hyperlinks(1).SubAddress
    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 :)

  39. 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

  40. 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 :)

  41. 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?

  42. 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!

  43. 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

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

    Thank you, thank you. This is great.

  45. 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!

  46. 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 :)

  47. 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.
    =HYPERLINK(“https://www.lexisnexis.com/totalpatent/wordLinkRedirect.html?&type_=patent&authority_=US&country_=US&num_=5411693&kind_=A&lbu_=TP”,”US5411693A”)

    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?

  48. 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 :)

  49. 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 :)

  50. 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 :)

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

    I was trying in office 2010 but not working to get the link.. please help

    • 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?

  52. 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

      Glad it helped, Rohit!

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

    awesome.. :)

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

      Glad you found it helpful, Khan :)

  54. 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!

  55. 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!

  56. 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.

  57. 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.

  58. 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 :)

  59. 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.

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

    Wow it worked :D 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! :)

  61. 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! :)

  62. 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!

  63. 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 :)

  64. 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!

  65. 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!

  66. 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! :)

  67. 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
    I followed your directions
    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)

    If rng.Hyperlinks.Count = 0 Then
    GetURL = “B”
    Else
    GetURL = rng.Hyperlinks(1).Address
    End If
    End Function

    Behind that in another box is
    Book 2 – Module1 (Code)
    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
    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])

  68. 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 :)

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

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

  70. 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])

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

    Good stuff. Big thanks to Karen.

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

    Thanks a lot!! Could save my time.

  73. 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!

2 Trackbacks

  • […] This should give you a nice table, including data and links to “Offender Information” and “Last Statement” pages for each record. These subpages contain additional information that we want.  You can extract hyperlinks from “Link” column in Excel using a macro described in this tutorial. […]

  • By Unbearbeitete Links | Yavuz Bogazci on February 27, 2014 at 1:52 pm

    […] sort on a projected field? – Sharepoint 2010 Forum Break Up a Text String in Microsoft Access How to extract a URL from a hyperlink on Excel Developer’s Tips and Tricks: LinkButton in SharePoint – Not firing Click Event […]

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>