Simple Find and Replace Question

[Elessvie]

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

 

[Gary''s Student]

Specify look in values

 

[Elessvie]

Thanks for response! But all that's available in the pull-down for Look In
is Formulas. How do I get it to show Values?

Apologies for all these dumb questions!

-Lynne

 

[Elessvie]

I meant to add that I am able to set Look In Values in the Find pull-down,
but in the Replace With pull-down, it shows only Formulas (Values is not
listed).

What am I doing wrong?

Any help really appreciated!

-Lynne

 

[Leanne]

Is this question still unanswered?

I meant to add that I am able to set Look In Values in the Find pull-down,
but in the Replace With pull-down, it shows only Formulas (Values is not
listed).

What am I doing wrong?

Any help really appreciated!

-Lynne

 

[Dave Peterson]

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]

Dear everyone,
Thank you SO much! I will DEFINITELY try the macro and also look at the
website link. Just wanted right now to thank you, even though I have no
feedback on the macro at the moment.
-Lynne

 

[Elessvie]

Hi, Dave,

This macro works beautifully! There are MANY ways we can use it here.

Thanks again,
-Lynne

 

[Dave Peterson]

But what a pain, huh?????

Hi, Dave,

This macro works beautifully! There are MANY ways we can use it here.

Thanks again,
-Lynne

 

[Elessvie]

Dave, I'm so happy to have something like this, that I can't complain!

-Lynne