Multiple replace of different things.

[Pampos]

I have asked before but I really need an easy way to do that.

I have an excel file with many values.

I want to replace some values with some others.

For example

dog with cat
car with airplane
city with town
me with you

Is there an EASY way to do this the same time? Not with replace each word one by one.

 

[Marc L]

As the easy way is the Excel Replace function …​

 

[p45cal]

I have asked before but I really need an easy way to do that.

I remind you of responses to that previous asking (https://chandoo.org/forum/threads/multiple-replace-of-different-things.35451/):

VBA will do it , but for that please upload your workbook , with the data in it.

and

Writing code is made easy if details of your data layout and some amount of data for testing is available.

 

[Pampos]

I remind you of responses to that previous asking (https://chandoo.org/forum/threads/multiple-replace-of-different-things.35451/):
and

The last sheet has two columns. The first one has words that appears to the other sheets. The second one has the replace words.

 

[NARAYANK991]

Hi ,

Can you not do it with a formula ?

See the first sheet of the attached file.

Narayan

 

[Pampos]

This is not an Easy way. I have 500 sheets. After I write your formula (I have to change 300 different words) I have to apply it to 500 sheets!! Is easiest to replace one by one the 300 words by the replacement tool...

 

[NARAYANK991]

Hi ,

If code has to be written , then your sample file has to be as close to your working file as possible.

Now you are mentioning 300 words which will need to be replaced ; are these 300 words in one worksheet tab , which in your sample file is named Replace ? Or are they spread over many tabs ?

Narayan

 

[Pampos]

Most of the them in every sheet. Some of them are in some sheets.

 

[NARAYANK991]

Hi ,

The words which need to be replaced will surely be spread over 500 sheets ; what I meant was whether the complete list of the 300 words which need to be replaced , and their replacements , are all listed in one worksheet , which in your sample file is named Replace ?

Narayan

 

[Pampos]

Yes. They are.

 

[NARAYANK991]

Hi ,

See the attached file and comment whether this is what you wanted.

You need to run the macro named ReplaceWords.

Narayan

 

[Pampos]

It seems to work fine. Just one thing. I am not so good in macro. How can I add more values in replace columns?

 

[NARAYANK991]

Hi ,

I have defined a named range called ReplaceTable , and in its Refers To box , the formula is :

=Replace!$A$1:$B$12

You can add as many words as you want to this list in the tab named Replace.

Suppose you add words till row 297 ; just change the above definition to :

=Replace!$A$1:$B$297

No other changes need to be made.

Narayan

 

[p45cal]

Button in attached, code is:

Sub blah()
ReplacementArray = Sheets("Replace").UsedRange.Columns("A:B").Value
For Each sht In Sheets
  If sht.Name <> "Replace" Then
    For i = LBound(ReplacementArray) To UBound(ReplacementArray)
'      Application.StatusBar = sht.Name & ", " & i
      sht.UsedRange.Replace What:=ReplacementArray(i, 1), Replacement:=ReplacementArray(i, 2), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Next i
  End If
Next sht
End Sub

 

[Pampos]

I can find ReplaceTable but not the formula.

 

[NARAYANK991]

I can find ReplaceTable but not the formula.

Hi ,

If you click on Name Manager , and then see the first named range , it will be the named range ReplaceTable. Down below , you should see the Refers To box , where you can see the formula :

=Replace!$A$1:$B$12

Narayan

 

[Pampos]

Thank you very much for you time and patient!!!
I will apply it and if there is a problem I will ask you again.

Thanks!