If you’ve used Copy and Paste from a web page into Excel, you may end up with links in your Excel sheet. You can also add a URL to a cell.

So how do you access the URL itself? What if you want to modify the URL?

You can do it, even though there’s no built-in function for it in Excel. You have to create your own Function macro:

Function GetAddress(HyperlinkCell As Range)
GetAddress = Replace _
(HyperlinkCell.Hyperlinks(1).Address, “mailto:”, “”)
End Function

To add this function to your spreadsheet:

Press Alt+F11 and select Insert>Module and paste the code snippet above. Push Alt+Q and save. The Function will appear under “User Defined” in the Paste Function dialog box (Shift+F3). Use the Function in any cell as shown below.

To use the actual function, type:

=getaddress(A1)

…where ‘A1′ is the cell that has the link you want to see.

For example,cell A1 has the text “101 Excel Tips” in it, but is hyperlinked to “http://www.best-free-information.com/review/exceltips.htm“. You create the function above and in cell A3 you enter “=getaddress(a1)”, then cell A3 will display the text “http://www.best-free-information.com/review/exceltips.htm”. Then you can actualy do text manipulation to it from there.

Note: if you found this useful, you might want to check out the 101 Excel Tips site.

[Slashdot] [Digg] [Reddit] [del.icio.us] [Facebook] [Technorati] [Google] [StumbleUpon]

20 Comments to “How to get URL from an Excel link”


  1. Adam — March 16, 2008 @ 12:14 pm

    Hi, I tried this out but I got a compile error when trying out getaddress in an actual cell. Am I supposed to paste that text into VBD as is or insert a variable such as the range that I’m working with?

    I have Office 2003 if that helps.

  2. admin — March 16, 2008 @ 1:54 pm

    Paste it as is. I don’t think the function can handle a range, though…try using only one cell as an argument (ie: getaddress(a1), not getaddress(a1:a40))

  3. Karl — April 21, 2008 @ 5:44 am

    Great function, but needs to be on one line and normal double quotes, not the funny ones used in the example, i.e.

    Function GetAddress(HyperlinkCell As Range)
    GetAddress = Replace(HyperlinkCell.Hyperlinks(1).Address, “mailto:”, “”)
    End Function

  4. Mick — April 24, 2008 @ 2:46 pm

    Cool mate! Function works like a charm…

    Thought Karl is right, the double quotes need to be replaced with real ones.

    Function GetAddress(HyperlinkCell As Range)
    GetAddress = Replace(HyperlinkCell.Hyperlinks(1).Address, “mailto:”, “”)
    End Function

  5. dan — May 8, 2008 @ 12:08 am

    hi there….is there any combo of browser and spreadsheet that allows you to copy linked text and paste it as the URL or change it at least to URL when its in the browser???

    I have excel 2008 on mac so the above function wont work for me apparently.

    Any help would be most appreciated

  6. Chris Martin — January 16, 2009 @ 12:54 am

    Great function. It really helps. Keep it up….

  7. Kenneth Andersen — April 24, 2009 @ 7:14 pm

    I’ve looked at a lot of examples of this function, and after several hours, i still keep getting this “#NAME!” error when utilizing the function from within the sheet.

    The funktion is copy/paste from 4-5 different versions, and i did remember to change the quoteation signs and stuff like that.

    I’m running on a english vista with a Danish version of the office-pack however i seems to know the parameters related to the line:
    “HyperlinkCell.Hyperlinks(1).Address”

    Have anyone seen this fault before? And more importantly, anyone know the reason and the fix?

    Sincerely,
    Kenneth Andersen

  8. Srini Kasturi — June 30, 2009 @ 12:33 pm

    The following worked

    Function GetAddress(HyperlinkCell As Range)
    GetAddress = Replace _
    (HyperlinkCell.Hyperlinks(1).Address, “”, “”)
    End Function

    Srini Kasturi

  9. Ben Levy — December 8, 2009 @ 2:02 pm

    Srini, if you don’t need te remove “mailto:” you can just use this function:

    Function GetAddress(HyperlinkCell As Range)
    GetAddress = HyperlinkCell.Hyperlinks(1).Address
    End Function

    How about a version that checks to see if a cell has hyperlink?

  10. Madhu — April 19, 2010 @ 1:55 am

    Works fine. But in a spreadsheet of close to 1000 items, this exercise of doing it cell by cell is very tedious. Any workarounds?

  11. trevor — April 21, 2010 @ 8:35 pm

    If the links are all in one column, type the formula once, and use the fill handle.

  12. Alex — June 13, 2010 @ 8:10 am

    Thanks, did just what was needed.

  13. Fran├žois — July 9, 2010 @ 2:00 am

    OOOoooohh So cool!!
    Thanks!!
    It’s alive!!

    @Madhu just select the right corner dot and drag it…

    Sorry for my english, i’m French!
    Hello from FRANCE ;-)

  14. Michel — August 30, 2010 @ 8:47 am

    Thanks alot, works like a charm
    With help from Karl’s comment

  15. piranha golf japan — September 24, 2010 @ 7:14 am

    Quicker way is to hit Ctrl K in the cell with the text you want to put a hyperlink in and a window will open up that lets you do just that.

  16. Thomas — October 15, 2010 @ 2:01 pm

    I managed to solve the #NAME problem by renaming the function to ‘GetURL’, there appears to be a problem in some language versions of Windows when using ‘GetAddress’ as the function name.

  17. John Blyler — March 18, 2011 @ 12:09 pm

    Great macro. I tried the fill handle for the entire column but it didn’t work. Help?!

  18. Ike — April 12, 2011 @ 11:36 am

    Yep, just replace the quotes at the end of the second line with the standard double quote on your keyboard next to the enter key (at least, for those of us using American languages/keyboards) ;)

  19. Mohan — August 29, 2011 @ 6:45 am

    The function works Cool! Thank you!

  20. yvolk — September 21, 2011 @ 5:06 am

    Thanks for the tip!
    Here is improved version of the function that chacks for the URL existence:

    Function GetAddress(HyperlinkCell As Range)
    addr = “”
    If (HyperlinkCell.Hyperlinks.Count > 0) Then
    addr = HyperlinkCell.Hyperlinks(1).Address
    End If
    GetAddress = addr
    End Function



Write a comment


Name




    
Best Free Information is based on WordPress platform, RSS tech , RSS comments design by Gx3.