Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Find and replace hyperlink in Excel 2007

Posted on 2013-12-17
5
Medium Priority
?
528 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
  • 2
  • 2
5 Comments
 
LVL 8

Assisted Solution

by:5teveo
5teveo earned 400 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 1600 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

963 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