Do you get a "formula too long" error message when you do it (maybe manually)?

(Saved from a previous post, so you'll have to edit some of it.)

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 = "$$$$$"
AfterStr = " " 'or chr(10) 'for alt-enter

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!

RITCHI wrote:

On Nov 24, 9:06 pm, JLGWhiz wrote:
Maybe if you restated the question so that your objective is clear someone
would offer some advice.



"RITCHI" wrote:
The snippet of code below has worked well for ages but now I have some
cells with hundreds of characters in it,seems to fail. Any ideas/help
would be appreciated.
Thanks

Sub FindReplace()

Cells.Replace What:="$$", Replacement:=" ", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

End Sub- Hide quoted text -

- Show quoted text -

I frequently need to import rich text files (Word) into Excel. In
Word I replace all manual line breaks with $$, then once in Excel use
this subroutine to replace the $$ with a couple of spaces.
The reason for doing so is to avoid loads of problems caused by non -
printing characters, text in the wrong cell etc.

There seems to be a limit on the maximum number of characters in any
one cell beyond which the script fails; it seems to be OK up to 512
characters but not 1023?

--

Dave Peterson