Converting Hyperlinks to URL

[Bobdog]

Q? I have a several hundred entity names that each function as a hyperlink. Each name is associated with a distinct web URL. While this is cool, I want to format the cells to show only the web URL. How do I convert these hyperlinks to the URL address? Is their a paste special function or command I can use?

Thanks!!!

 

[jasonb75]

You can paste this code into a VBA module

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

then, if your first link is in A1 enter =GetAddress(a1) into B1 and autofill down.

Is this what you need?

JB

 

[Bobdog]

Thanks... That looks right... Now I'll learn how to use macros.

 

[Bobdog]

Code required a change in the function name to GetAddress2. Not sure why, but I assume the name was already in use???

Thanks. Once the function name was changed it worked slick!