• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 537
  • Last Modified:

Find and replace hyperlink in Excel 2007


Trying to save my Excel 2007 has an xml file.  When I do a "Save As" I select xml -- then I get a warning saying "Cannot save XML data because the workbook does not contain any XML mappings."

Basically what I want to do a search and replace of hyperlinks.

For example, My hyperlink would be http://www.bananas.com/apples.html

And I want to change it to


I googled hyperlink search and replace and was told one method is to save as hyperlink and then open in notepad or similar and do the search and replace there.

However as I write this question, perhaps there is an easier way to do it?  Perhaps with a visual basic script????

Or a formula?


Rowby Goren
Rowby Goren
  • 2
  • 2
2 Solutions
Try this for starters... MAcro runs agains data where link searched is assumed to be in column 'A'. I attached Excel Spreahsheet w/ macro

Sub Macro1()
' Macro1 Macro

 RowIndexDestination = 1

 Worksheets("Data1").Cells(1, "A").Value = "Extracted Data"

'Get Date
 For rx1 = 1 To 10000
     With Worksheets("Report").Cells(rx1, "A")
          'Check for Last Line
         If .Value = "" Then
             Exit For
         End If
         If InStr(1, Worksheets("Report").Cells(rx1, "A"), "http") > 0 Then
            RowIndexDestination = RowIndexDestination + 1
            Worksheets("Data1").Cells(RowIndexDestination, "A") = Right(Worksheets("Report").Cells(rx1, "A"), Len(Worksheets("Report").Cells(rx1, "A")) - InStrRev(Worksheets("Report").Cells(rx1, "A"), "/") + 1)
         End If
     End With
 Next rx1
End Sub
Searches the entire sheet for URLs up to the last cell and replaces both the URL and the display value via regex:
Public Sub TrimUrls()
    ' Find the data
    Dim rowMax As Integer, colMax As Integer
    Dim ws As Worksheet
    Set ws = Application.ActiveSheet
    rowMax = ws.Cells.SpecialCells(xlLastCell).row
    colMax = ws.Cells.SpecialCells(xlLastCell).Column

    ' Loop replacing
    Dim row As Integer, col As Integer
    Dim ce As Range, re As Variant
    Dim result As String
    For row = 1 To rowMax
        For col = 1 To colMax
            Set ce = ws.Cells(row, col)
            If ce.Hyperlinks.Count > 0 Then
                Set regEx = CreateObject("vbscript.regexp")
                With regEx
                    .IgnoreCase = True
                    .MultiLine = False
                    .Pattern = "^[^#]*?://.*?[^/]*"
                End With

                result = regEx.Replace(ce.Hyperlinks(1).Address, "")
                ce.Hyperlinks(1).Address = result
                ce.Value = result
            End If

End Sub

Open in new window

Rowby GorenAuthor Commented:

I'm getting an error with 5teveo's version (perhaps I copied and pasted it wrong. (Subscript out of range.)

And Angelplay is also changing the text of the hyperlink.   I need to keep the visible text untouched.

I should have uploaded a sample file.  The attached includes both vb solutions.

Thanks for helping!

Just remove ce.value=result from mine.
Rowby GorenAuthor Commented:
Thanks!  It took care of the issue!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now