"Find and Replace" macro finds items nested in larger words!

[IrishMist1748]

Hello!

I am using the following macro to auto find and replace multiple items at once with Sheet2 containing the items I want to "find and replace" (with 'finds' in column A and 'replaces' in column B) and Sheet1-column B is the text I am running the "find and replace" macro against:

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

My problem/question is how do I change the code so it will only "find" whole "words" and do the appropriate replacement? Currently it is finding "c" mixed amongst other words instead of finding 'c' by itself.

Example:
Find: C, Replace with: complete
Find: CL, replace with: clear

Text searched: CL C Clock

Current results: completeL complete completelo completek
Desired result: clear complete clock

Thank you!
<table x:str="" style="border-collapse: collapse; width: 368pt;" width="490" border="0" cellpadding="0" cellspacing="0"><col style="width: 368pt;" width="490"><tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 368pt;" width="490" height="17">
</td> </tr></tbody></table>

 

[IrishMist1748]

I don't quite understand? I just want it to find "C" and replace with "complete". If "C" is contained in any other word, it should ignore. When this is working correctly, I will be putting about 300 find-replace conbos in table 2.

Can this be done so (as in my example) it just finds "C"?

 

[Jonmo1]

Try

Worksheets("Sheet1").Range("B:B").Cells.Replace What:=FindStr & " ", Replacement:=RepStr & " ", LookAt:=xlPart

 

[IrishMist1748]

YES_YES_YES! That works!Thanks to both of you (Andrew and jonmo)! You guys are GREAT!

I will now set it up to work on about 150 find-replace combos (more later) and see if there are any more bugs!

I also tried the program you sent, it it works good great too! Do you know when the registered version will be available?

THANK YOU FOR YOUR HELP!

 

[Andrew Poulsom]

If you want to find the whole word "C" in a sentence you would need put spaces around the sentence and look for " C ".

 

[Jonmo1]

Yep, my solution is far from perfect...

Note, with my last post, it will find and replace any occurance of "c ".

So it will find "Tic Tac Toe"


Also, it will NOT find the C at the END of the string like

"X Y Z C"
It will not find that C.

 

[IrishMist1748]

Thanks Andrew!
That makes sense! Is there an easy way to do that for say, 300 single words and short sentences?

 

[Andrew Poulsom]

Thanks Andrew!
That makes sense! Is there an easy way to do that for say, 300 single words and short sentences?

Not using the Replace method. You would have to loop around the cells and use the Replace function.

 

[IrishMist1748]

So if I use the format " C " instead of "C ", will that solve this problem?

Can I easily put a space before and after my "search term"?

 

[IrishMist1748]

Not using the Replace method. You would have to loop around the cells and use the Replace function.

How would I do that? And then would I still use:

Worksheets("Sheet1").Range("B:B").Cells.Replace What:=FindStr & " ", Replacement:=" " & RepStr & " ", LookAt:=xlPart

 

[IrishMist1748]

Can I CONCATENATE with a space before and after the word directly using the space bar on the keyboard?

I guess so, it works well! Good enough for this part of my project!

Thanks guys for all of your help!