How do you Find and Replace the word "and" but not the and in "han

[Lyn]

I thought there was a - match whole word - option with the Find and Replace
tool but I don't see it now. I'm trying to replace every "and" with "&" but
not affect any words that contain "and" such as "hand" or "bandit".

 

[Gary''s Student]

Be sure to type a single space before and after the and

 

[Lyn]

Thanks so much! That did it!

Be sure to type a single space before and after the and

 

[John Bundy]

I'm not aware of a way to do that, someone here might enlighten us. What i do
know is that you have an and as a word it will have spaces on both sides so
you can use " and " to get all those. Be warned that anything that starts
with and or has punctuation after it will not get changed.

 

[Ron Rosenfeld]

I thought there was a - match whole word - option with the Find and Replace
tool but I don't see it now. I'm trying to replace every "and" with "&" but
not affect any words that contain "and" such as "hand" or "bandit".

If you do not want to change "and" when it is at the beginning or end of the
string, then

Find what: <space>and<space>
Replace with: <space>&<space>

If you also want to replace "and" when it occurs at the beginning and end of
the line, then you can use this macro on a cell selection:

==============================
Option Explicit
Sub reReplace()
Dim c As Range
Dim re As Object
'This pattern will replace all "and" including
' those at beginning and end of string
'To omit those at the beginning and end
'Const sPat As String = "\sand\s"
'Const sRepl As String = " & "
Const sPat As String = "\band\b"
Const sRepl As String = "&"

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.Global = True
re.ignorecase = True
For Each c In Selection
c.Value = re.Replace(c.Value, sRepl)
Next c

End Sub
===================================
--ron