Making cell show hyperlink address instead of cell value

Hey guys,

I have a range of 400 cells in column D which have values and hyperlinks embedded in the cells. I want the cell to show the actual hyperlink address instead of the cell value. I tried using the following macro code but it does not work. Anyone has any idea???

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+j
'
Range("D2:D498").Select
Selection.Hyperlinks(1).TextToDisplay = ""
End Sub

Hi,

In the sample excel, the value in cell A1 is jack. But the embedded hyperlink is www.google.com I want cell A1 to show www.google.com. Now for one cell it is very easy as you simply have to right click, edit hyperlink and delete the value in text to display. I tried doing this and recording a macro and then running it to the range A1:A500 but it does not work.