Saved from a previous post:

You can use a macro to do the change:

Option Explicit
Sub testme01()

Dim FoundCell As Range
Dim ConstCells As Range
Dim BeforeStr As String
Dim AfterStr As String

BeforeStr = "OneWord"
AfterStr = "aDifferentWord"

With ActiveSheet
Set ConstCells = Nothing
On Error Resume Next
Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _
xlTextValues)
On Error GoTo 0

If ConstCells Is Nothing Then
MsgBox "Select some cells in the used range"
Exit Sub
End If

With ConstCells
'get as many as we can in one step
.Replace what:=BeforeStr, Replacement:=AfterStr, _
lookat:=xlPart, SearchOrder:=xlByRows

Do
Set FoundCell = .Cells.Find(what:=BeforeStr, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlPart, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'done, get out!
Exit Do
End If
FoundCell.Value _
= Replace(FoundCell.Value, BeforeStr, AfterStr)
Loop
End With
End With
End Sub

If you're using xl97, change that Replace() to application.substitute()

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ps. Try it against a copy of your data--just in case!

Elessvie wrote:

Hello, folks!

I'm trying to find and replace a word with another in a column containing
text. I'm using Ctrl-H, Find = reciept (for example), Replace with =
receipt. This locates a cell containing the word, but when I press Replace,
Excel displays the error message "Formula is too long."

I've tried formatting the column as General and as Text and get the error
message with both. I've searched Excel Help and this discussion group, but
haven't been able to find this specific issue addressed, so I'm thinking it's
probably something really obvious!

Thank you for any help.

-Lynne

--

Dave Peterson