[Twiztnin223]

Hello all

I have about 400 cells of hyperlinks that I would like to convert the "Text to Display" on all of them to "(Info)" rather than it showing the hyperlink. Is there an easier way to change all of the cells rather than one at a time?



Thanks!

[Advertisements]

Twiztnin223,
The only way I could work out how to do this was with a macro.
If you are familiar with macros, I have written a few lines that may get you started.

If you are not familiar this link may help
http://www.exceltip...._Excel/631.html


Sub macCopyAlterHyperlinks()
'
' Macro recorded 29/04/2006 by dsm
'
'This macro will copy Hyperlinks from Column A to Column B
'There is no error checking so the following assumptions have been made
'-a hyperlink exists in every cell in column A in the rows specified by "for i = xx to yy"
'-a SubAdddress and ScreenTip fields are empty and will not be copied
'.Hyperlinks.Add .Range("B" & I), myAddress, mySubAddress, myScreenTip, myTextToDisplay

For I = 1 To 400

Debug.Print "A" & I & "-Address", Range("A" & I).Hyperlinks(1).Address
Debug.Print "A" & I & "-SubAddress", Range("A" & I).Hyperlinks(1).SubAddress
Debug.Print "A" & I & "-ScreenTip", Range("A" & I).Hyperlinks(1).ScreenTip
Debug.Print "A" & I & "-TextToDisplay", Range("A" & I).Hyperlinks(1).TextToDisplay

Range("B" & I).Delete

myAnchor = Range("B" & I)
myAddress = Range("A" & I).Hyperlinks(1).Address
mySubAddress = Range("A" & I).Hyperlinks(1).SubAddress
myScreenTip = Range("A" & I).Hyperlinks(1).ScreenTip
myTextToDisplay = "info"

With Worksheets(1)
.Hyperlinks.Add .Range("B" & I), myAddress, , , myTextToDisplay
End With

Debug.Print "B" & I & "-Address", Range("B" & I).Hyperlinks(1).Address
Debug.Print "B" & I & "-SubAddress", Range("B" & I).Hyperlinks(1).SubAddress
Debug.Print "B" & I & "-ScreenTip", Range("B" & I).Hyperlinks(1).ScreenTip
Debug.Print "B" & I & "-TextToDisplay", Range("B" & I).Hyperlinks(1).TextToDisplay

Next I

End Sub


Good Luck
David

[dsm]

Twiztnin223,
The only way I could work out how to do this was with a macro.
If you are familiar with macros, I have written a few lines that may get you started.

If you are not familiar this link may help
http://www.exceltip...._Excel/631.html


Sub macCopyAlterHyperlinks()
'
' Macro recorded 29/04/2006 by dsm
'
'This macro will copy Hyperlinks from Column A to Column B
'There is no error checking so the following assumptions have been made
'-a hyperlink exists in every cell in column A in the rows specified by "for i = xx to yy"
'-a SubAdddress and ScreenTip fields are empty and will not be copied
'.Hyperlinks.Add .Range("B" & I), myAddress, mySubAddress, myScreenTip, myTextToDisplay

For I = 1 To 400

Debug.Print "A" & I & "-Address", Range("A" & I).Hyperlinks(1).Address
Debug.Print "A" & I & "-SubAddress", Range("A" & I).Hyperlinks(1).SubAddress
Debug.Print "A" & I & "-ScreenTip", Range("A" & I).Hyperlinks(1).ScreenTip
Debug.Print "A" & I & "-TextToDisplay", Range("A" & I).Hyperlinks(1).TextToDisplay

Range("B" & I).Delete

myAnchor = Range("B" & I)
myAddress = Range("A" & I).Hyperlinks(1).Address
mySubAddress = Range("A" & I).Hyperlinks(1).SubAddress
myScreenTip = Range("A" & I).Hyperlinks(1).ScreenTip
myTextToDisplay = "info"

With Worksheets(1)
.Hyperlinks.Add .Range("B" & I), myAddress, , , myTextToDisplay
End With

Debug.Print "B" & I & "-Address", Range("B" & I).Hyperlinks(1).Address
Debug.Print "B" & I & "-SubAddress", Range("B" & I).Hyperlinks(1).SubAddress
Debug.Print "B" & I & "-ScreenTip", Range("B" & I).Hyperlinks(1).ScreenTip
Debug.Print "B" & I & "-TextToDisplay", Range("B" & I).Hyperlinks(1).TextToDisplay

Next I

End Sub


Good Luck
David