Solved

Find and replace hyperlink in Excel 2007

Posted on 2013-12-17
5
521 Views
Last Modified: 2013-12-17
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
Comment
Question by:Rowby Goren
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 8

Assisted Solution

by:5teveo
5teveo earned 100 total points
ID: 39724528
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
 
LVL 11

Accepted Solution

by:
Angelp1ay earned 400 total points
ID: 39724571
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
 
LVL 9

Author Comment

by:Rowby Goren
ID: 39724698
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
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39724710
Just remove ce.value=result from mine.
0
 
LVL 9

Author Closing Comment

by:Rowby Goren
ID: 39725470
Thanks!  It took care of the issue!

Rowby
0

Featured Post

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question