Macro to remove characters

[Guest]

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.

 

[JLatham]

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).

 

[Guest]

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.

 

[Gord Dibben]

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

 

[Guest]

The formula didnt do anything even though all we did was replace value to
formula. Maybe I am leaving out critical information that I am not aware of,
would someone be willing to look at my Excel Grid that I am trying to fix.

 

[Gord Dibben]

You are trying to get replace line feeds...........chr(10)........in selected
cells with a space. Right?

Are the line feeds generated by formula like =A1 & CHAR()10) & B1 ?

Or generated some other way like manually entered Alt + Enters or from imported
data?

If the latter, the macro will work.

If you want to send me the workbook, feel free to do so.

Change the AT and DOT to appropriate punctuation to get my address.

Gord