Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a huge list of words that have links. All the URLs have text that repeats. I want to mass replace this text with another. Right now, "Replace all" only searches between words, not through the embed hyperlinks behind words.

Please help. Thanks.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

The only way to do this is with a Macro:

Public Sub ReplaceHyperlinkURL(FindString As String, ReplaceString As String)
Dim LinkURL, PreStr, PostStr, NewURL As String
Dim FindPos, ReplaceLen, URLLen As Integer
Dim MyDoc As Worksheet
Dim MyCell As Range
On Error GoTo ErrHandler

Set MyDoc = ActiveSheet
For Each MyCell In MyDoc.UsedRange
If MyCell.Hyperlinks.Count > 0 Then
 LinkURL = MyCell(1).Hyperlinks(1).Address
 FindPos = InStr(1, LinkURL, FindString)
 If FindPos > 0 Then 'If FindString is found
  ReplaceLen = Len(FindString)
  URLLen = Len(LinkURL)
  PreStr = Mid(LinkURL, 1, FindPos - 1)
  PostStr = Mid(LinkURL, FindPos + ReplaceLen, URLLen)
  NewURL = PreStr & ReplaceString & PostStr
  MyCell(1).Hyperlinks(1).Address = NewURL 'Change the URL
  End If
 End If
Next MyCell
Exit Sub
ErrHandler:
MsgBox ("ReplaceHyperlinkURL error")
End Sub

The ReplaceHyperlinkURL code must be placed in a VBA code module. From a spreadsheet, Press Alt+F11 to open the VBA Editor. Then select Insert - Module from the menu. Copy the code and paste it into the module. Then save the module.

In order to run the procedure, create a macro that contains following line and run the macro in Excel. Be sure to replace the FindText with the portion of the address you want to find and ReplaceText with the text you want to replace it with.

Call ReplaceHyperlinkURL("FindText", "ReplaceText")

Source

share|improve this answer
    
I'm in stuck the macro part. I don't know how to create the macro and run it. How can do this? –  Karolinger Oct 11 '13 at 5:22
1  
@Karolinger Create or delete a macro - Microsoft Office Support –  Moses Oct 11 '13 at 5:25
    
Holy Moses, @Moses. You saved my life! Thank you very much, man! –  Karolinger Oct 11 '13 at 5:38
1  
@Karolinger Glad I could help :) –  Moses Oct 11 '13 at 5:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.