Multiple Find & Replace text in cells from a list

[ericcellis]

Hello,
I really need help! I build my website off of a very large excel file. I add new product frequently and the text I get from my vendor has symbols and characters that cause the web pages to fail being generated. I know what symbols and characters to do find and replaces for but there are about 20 different find and replaces that I have to do. This ends up taking FOREVER!!!

I want to know, is there a way to make a seperate workbook that has a list of the problem symbols and characters, and then somehow have find replace go down the list and do each find and replace on the workbook that builds my webpages? I would like the list to be in a seperate workbook because I have multiple workbooks for my website so it would be easier if I only had 1 workbook to update the find and replace data.

This procedure really takes a lot of time so it would really help me out if I could automate it somehow.

Here is an small example of what I am needing to find and replace (note: Each item below, in the html code, would have a ; at the end but I had to remove it so the code would show up on this site.)
Ampersand + a space: &amp
Quote:" to &quot (this is actually a non-standard quote - like the ones where the quote at the beginning is one direction and the end one is the other direction or upside down.)
Copywrite:© to &copy
Registered:® to &reg
Trademark:™ to &trade
à to &agrave
è to &egrave
á to &aacute
é to &eacute
í to &iacute

Thank you so much for your help!!!
Eric

 

[pboltonchina]

Try ASAP Utilities, a free add in for Excel. Under the 'Text' menu is 'Advanced Character Removal and Replacement'. This seems to cover all the characters you want to deal with.

http://www.asap-utilities.com

 

[ericcellis]

Thanks for your reply! I took a look at the program but I would still have to do each find replace seperately because each character or symbol would have to be replaced with it's html counterpart. I'm looking for a way to have a list of items to find in column A and the html counterpart in column B. I want to get this automated because this is a huge time waster. I am disabled and work for myself. The more time I waste with the find and replace means the longer it takes for me to get items up for sale.

I think I will install that program cuz it looks like it will have some other things I could use, but it doesn't do the find replace job I need.

Any other ideas?

 

[dave3009]

Assuming you have a list of 20 search items and replce items on sheet2 and you're looking to replace the values on sheet1

Sub Macro1()
Dim i As Integer
Dim FindStr As String
Dim RepStr As String
For i = 1 To 20
    FindStr = Sheet2.Range("A" & i).Value
    RepStr = Sheet2.Range("B" & i).Value
    
    Sheet1.Cells.Replace What:=FindStr, Replacement:=RepStr
Next i
End Sub

If you've just been using Find/Replace to do this it should work, it's basically that process automated

 

[ericcellis]

Thanks, I will give that a shot! Sorry to ask, but I am not sure how to do this. If the list was on sheet1 of workbook list.xls and the text to be replaces was in pricelist.xls on a worksheet called MASTER.

Is it possible for the below to do that? How do I enter other filenames in the code. Again, sorry I am more of a data entry guy when it comes to excel.

Thanks again for your help!

 

[dave3009]

Should be OK, try

Sub Macro1()
Dim i As Integer
Dim FindStr As String
Dim RepStr As String
For i = 1 To 20
    FindStr = Workbooks("list").Sheets("Sheet1").Range("A" & i).Value
    RepStr = Workbooks("list").Sheets("Sheet1").Range("B" & i).Value
    
    Workbooks("pricelists").Sheets("MASTER").Cells.Replace What:=FindStr, Replacement:=RepStr
Next i
End Sub

 

[ericcellis]

You are awesome. I will give it a shot when I get back to work friday. I was just about to go to bed when I got the alert for your post. It will be so great if it does what I need. I am so sick of wasting hours doing manual find and replaces.

Thanks again!

 

[dave3009]

Another alternative will be to have the pricelists.xls active and refer to it as the Activeworkbook. The code would then look like

Sub Macro1()
Dim i As Integer
Dim FindStr As String
Dim RepStr As String
For i = 1 To 20
    With ThisWorkbook.Sheets("Sheet1")
        FindStr = .Range("A" & i).Value
        RepStr = .Range("B" & i).Value
    End With
    
    ActiveWorkbook.Sheets("MASTER").Cells.Replace What:=FindStr, Replacement:=RepStr
    
Next i
End Sub

You might also consider turning it into an addin, but maybe just one step at a time

 

[ericcellis]

How hard would it be to create a Add-In? That really would be the best solution since the excel workbook name changes often, usually the worksheet remains MASTER. If it was an add-in, then I am guessing it would be something I could use on any workbook.

I am messing with this some tonight, it's been a bit crazy due to the holiday and some other problems.

Thanks,
Eric

 

[dave3009]

If you save the workbook as an addin (selected from the dropdown in the SaveAs dialogue box) to

C:\Program Files\Microsoft Office\Office12\XLSTART

It will start every time you start Excel. You could also save it to the AddIns folder which would make it available in the AddIn's dialogue section.

You will need to adjust the code slightly to work on the ActiveWorkbook.ActiveSheet rather than Sheets("Master").