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:”, “”)
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:
…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.