Find Whole Word Only

[Lonely]

Hi All,
I need to replace the text "NS" from all the cells in all the worksheets of an Excel workbook. I wish to design a macro that can help me do this along with the use of something that would allow me to do the "Find Whole Word" option similatr to the one we have in MS Word.

A probable way could be to wrap spaces around the word: " NS " (but that'll have
trouble with " NS.", " NS, "--any punctuation. But this is still acceptable.


Please can someone help.
Thanks,
Lonely

[Lonely]

Hi All,
I am trying to stimulate the "Find Whole Word" option similar to the one we have in MS Word to perform Find/Replace of abbreviations in Excel files. I was able to come up with the following:

*****************************
Sub Test()
Dim WorkRange As Range
Dim c As Range
Dim Punc As Variant
Dim Pos As Integer
Dim x As Integer
Dim y As Integer
Dim Str As String
On Error Resume Next
Set WorkRange = Selection.SpecialCells(xlCellTypeConstants, 2)
If Err <> 0 Then
MsgBox "No cells found"
Exit Sub
End If
On Error GoTo 0
Dim Ans As Variant
Ans = InputBox("Replace what", "Replace whole word")
If Ans = "" Then Exit Sub
Punc = Array(" ", ",", ";", ":", ".")
For Each c In WorkRange
For x = LBound(Punc) To UBound(Punc)
For y = LBound(Punc) To UBound(Punc)
Str = Punc(x) & Ans & Punc(y)
Pos = InStr(1, c.Value, Str, 1)
If Pos > 0 Then
c.Value = WorksheetFunction.Substitute(c.Value, Ans, "")
Exit For
End If
Next y
Next x
Next c
End Sub
************************************8

However, this code has the following problems:
1) The code works well only if the term is preceeded by and followed by another term. If the term NS comes in the starting or the end of the cell, the replacements are not affected.

2) Also, my excel sheet had "this is good" in one cell, "is this good" and "good is good" in the other cell. When I run the macro to replace "is" with X, the text in these 3 cells becomes "thX X good" and "is this good" and "good X good" respectively.

Can someone please help me out in this?
Regards,
Lonely

[si_the_geek]

what you should do is add punctuation to the start and end of the string, so that it will be found there if needed

you just need to alter one line:
Pos = InStr(1, Punc(x) & c.Value & Punc(y), Str, 1)


by the way, you could also do this another way, rather than search for the string with specific punctuation before & after, you could just find the string itself, and check the character before and the one after (and repeat from this position if it didnt match).

[Lonely]

Thanks, si_the_geek,
I will giove it a try,