Remove line breaks, return carriages, and all leading space in Excel Cell

Question

I have no idea what is happening, but I have cells that contain what appears to be a return carriage. I have tried TRIM(), CLEAN(), =SUBSTITUTE(A1,CHAR(10),"") and a number of macros to remove these characters.

The only way to remove these characters it to get the cell active, click delete near the last character, and click enter.

Is there something I'm missing? Is there a way to programatically do this?

Answer 1

The following macro will remove all non-printable characters and beginning and ending spaces utilising the Trim() and Clean() functions:

Sub Clean_and_Trim_Cells()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim s As String
    For Each c In ActiveSheet.UsedRange
        s = c.Value
        If Trim(Application.Clean(s)) <> s Then
            s = Trim(Application.Clean(s))
            c.Value = s
        End If
    Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Answer 2

An easier solution is find replace: for find press alt and the numbers 010 at the same time (on the 10 keypad) and then replace with a space.

You can do this as a bulk replace by just highlighting the cells that contain the carriage breaks.