Revised.................change Cell.Value to Cell.Formula

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.Formula, K, 1)
Else
NewString = NewString & " " 'replacement space
End If
Next K
rCell.Value = NewString
NewString = ""
Next rCell

End Sub


Gord Dibben MS Excel MVP

On Fri, 8 Jun 2007 16:59:02 -0700, YanYan
wrote:

Yes it is CHR10, the character is appearing at the beginning and other times
at the end of my data. This is the macro that I am using to remove it:

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

This macro removes my formulas.

"JLatham" wrote:

It would help to see the code for the macro you are currently using that is
also destroying your formulas. We could probably fix it easily. I suspect
your macro is simply removing the right-most character from what ever is in
the cell. You need to test the value in a cell before stripping that
character to see that it is CHR(10).

"YanYan" wrote in message
...
I have an Excel Workbook where the sheets are all linked to one main sheet.
The data on the main sheet ends up with character10 (the little square
box)
in each cell.

I have a macro that I run and it deletes all the characters for me, its
great I love it, but it also deletes all the formulas on that sheet which
I
need.

How can I delete that character without deleting my formulas.