Excel find and replace a selection of words

[RodP]

Hi,

Please could someone let me know if it's possible to build a macro that can automatically find a selection of words and replace them with alternatives. For example:-

[Find word] [Replace with]
Bonjour Hello
Adios Goodbye
Buenos Dias Hello

I have to regularly import data which is in different languages and (to use a pivot table) need to ensure that they are all in the same language. The word conversion list is maintained in an excel sheet setup in the above manner.

Hope someone can help

Many thanks

Rod

 

[dyarwood]

If you do record a macro then do it with button clicks it will build a macro itself to do this.

The code is something like the following

Cells.Replace What:="WHAT YOU WANT TO REPLACE", _ Replacement:="REPLACE WORD" , LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:= False, ReplaceFormat:=False

Let me know if that helps

dyarwood

 

[xlbo]

if you have the table set up as in your example, call it "myTable" and then summat like this should work


for each c in range("myTable&quot.columns(1)
fWhat = c.text repWhat = c.offset(0,1).text
with sheets("Sheetname&quot.cells
.Replace What:=fWhat, Replacement:=repWhat, LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
end with
next

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[RodP]

Many thanks to you both. THat will help nicely.

Cheers

Rod

 

[RodP]

Hi Guys,

I've set this macro up and thought it was working ok but have now found that it's looking at part words. For example:-

'Ja' is 'Yes' in Dutch. I want to replace Ja with Yes. However, I have the word Jams in english in the same spreadsheet and excel is replacing the Ja bit with Yes resulting in Yesms!

The macro needs to sift through each sell and look for specific words and not part words in the cell. However, there doesn't seem to be a simple function telling excel to only replace whole words within the cell. There is only a function which will look at the whole cell.

This is the code I am using:
-----------------
Private Sub CommandButton1_Click()

MsgBox ("Please copy the translation sheet into the work book first")
sname = InputBox("Please type in sheetname:")

Sheets(sname).Select

For Each c In Range("TransTable").Columns(1).Cells
fwhat = c.Text
repwhat = c.Offset(0, 1).Text
'MsgBox fwhat & " " & repwhat
With Sheets(sname).Cells
.Replace What:=fwhat, Replacement:=repwhat, lookat:= _
xlPart, searchorder:=xlByRows, MatchCase:=False
End With
Next c
End Sub
---------------

Hope someone can help. Cheers

RodP

 

[grrr72]

Hi RodP,

Why don't you try putting a space either side of the word ja as your variable ie ' ja '. This won't work if ja is the only word in cell, but from what you say above, that doesn't look to be the case.

Hope that helps

Nic

 

[RodP]

Hi grrr72

Unfortunately there are instances where this is the only word in a cell. perhaps I should d a double pass or something but that would take a very long time to do the search and replace - even with a p4!!!!

Any other suggestions?

Ta

RodP

 

[xlbo]

Unfortunately, excel only has a PartMatch & WholeMatch functionality within the Find and Replace - and the PartMatch gives the results you are finding - there is no Match Whole Word functionality within excel - however, I believe there is within Word....

If it is not feasible to port over to Word, you may have to do an inner loop which loops through the characters in the cell and an outer loop that loops through the cells...

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[grrr72]

RodP,

The only other way I can think of doing it within Excel is by taking the column you want to do the replacement in, separate the words by doing a 'text to columns' using a space as the delimiter. You can then do the replacement by matching the whole cell (as each cell will only hold one word), then use the concatenate function to put it back together.
Messy I know but unless you follow Geoff's advice and go to Word I can't think of an easier way

Regards,
Nic