Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works

Excel Hyperlinks

  • Please log in to reply




  • Member
  • PipPip
  • 90 posts
Hello all :blink:

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?


  • 0





  • Member
  • PipPip
  • 98 posts
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

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
  • 0

Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP