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.