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

excel, vba

Solution

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

Problem

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?

Original source

Related problems