CODEWITHSUNDEEP
excelvba
David Macarthur
David Macarthur

Reputation: 15

Changing TextToDisplay for Hyperlinks in Excel via VBA

Goal:
Replace the display text for any hyperlinks in an Excel sheet starting with www.google.com with Google while maintaining the original hyperlink URL and cell position.

I'm bashing together what I found online, like How To Change Multiple Hyperlink Paths At Once In Excel?.

I feel I'm close with:

Sub ReplaceHyperlinks()
Dim Ws As Worksheet
Dim xHyperlink As Hyperlink
Set Ws = Application.ActiveSheet
For Each xHyperlink In Ws.Hyperlinks
    xHyperlink.TextToDisplay = Replace(xHyperlink.TextToDisplay, "www.google.com/*", "Google")
Next
End Sub

Upvotes: 1

Views: 1416

Answers (2)

Tim Williams
Tim Williams

Reputation: 166341

Try this:

Sub ReplaceHyperlinks()
    Dim Ws As Worksheet
    Dim lnk As Hyperlink
    Set Ws = Application.ActiveSheet
    For Each lnk In Ws.Hyperlinks
        If LCase(lnk.Address) Like "*google.com*" Then 'Google link ?
            lnk.TextToDisplay = "Google"
        End If
    Next
End Sub

Upvotes: 2

findwindow
findwindow

Reputation: 3153

Use mid and find like

=MID(A1,5,FIND(".",A1,5)-5)

Edit: So use hyperlink like

=HYPERLINK(B1,PROPER(B1))

enter image description here

Upvotes: 3

Related Questions