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

Find and replace hyperlink in Excel 2007

Hi

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

/apples.html

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?

Thanks

Rowby
0
Rowby Goren
Asked:
Rowby Goren
  • 2
  • 2
2 Solutions
 
5teveoCommented:
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
'
'
 Worksheets("Data1").Select
 Cells.Select
 Selection.ClearContents

 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
         
         'Date
         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
EE-20131217-ChangeText.xlsm
0
 
Angelp1ayCommented:
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
        Next
    Next

End Sub

Open in new window

Example.xlsm
0
 
Rowby GorenAuthor Commented:
Hi

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!

Rowby
expert-exchange-1.xlsx
0
 
Angelp1ayCommented:
Just remove ce.value=result from mine.
0
 
Rowby GorenAuthor Commented:
Thanks!  It took care of the issue!

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