Formula is too long

[Henry]

Hello,

I have a large Excel 2000 spreadsheet that has a column that gives a
html description of each product. I am trying to run the following
macro to remove unwanted CR and LF, etc.

ActiveSheet.Cells.Replace Chr(160) , ""
ActiveSheet.Cells.Replace Chr(13) , ""
ActiveSheet.Cells.Replace Chr(10) , ""

This seems to work until the script comes to a description that is
very long. At that point I get "Formula is too long".

Is there a way around this issue?

Thanks for any help!
Henry

 

[Dan E]

Henry,

Select the range which you want the cr's, lf's, and crlf's removed from and run this

Sub ClearCRLF()
For Each Cell In Selection
temp = Cell.Text
For i = 1 To Len(temp)
a = Mid(temp, i, 1)
If a = vbCrLf Or a = vbCr Or a = vbLf Then
temp = Left(temp, i - 1) & Right(temp, Len(temp) - i - 1)
End If
Next
Cell.Value = temp
Next
End Sub

Dan E