DaveO

EditReplace or Text to ColumnsDelimited byOther will both find the 0010
character.

I think that OP may something other than CR's in his data if Alt + 0010 not
working.


Gord Dibben MS Excel MVP

On 21 Mar 2007 07:51:52 -0700, "DaveO" wrote:

I don't think Excel's search and replace capability will look for a
carriage return and replace it with another character. This utility
will find a carriage return (ASCII character 10) and replace it with a
space. Copy this code and paste it into your sprdsht as a macro, and
let us know how it goes. Note that due to the vagaries of Usenet
posting some unintended line wrapping may occur. This code tested fine
on my machine; any errors may be due to line wrapping.

Sub ReplaceCR()
Dim rCell As Range
Dim K As Long
Dim NewString As String

For Each rCell In Selection.Cells
For K = 1 To Len(rCell.Value)
If Mid(rCell.Value, K, 1) < Chr(10) Then
NewString = NewString & Mid(rCell.Value, K, 1)
Else
NewString = NewString & " " 'replacement space
End If
Next K
rCell.Value = NewString
NewString = ""
Next rCell

End Sub