Delete line breaks, return wagons and all leading space in Excel cell

Question

I have no idea what is going on, but I have cells that contain what seems like a return carriage. I tried TRIM() , CLEAN() , =SUBSTITUTE(A1,CHAR(10),"") and several macros to remove these characters.

The only way to remove these characters is to activate the cell, click Delete next to the last character and press Enter.

Is there something I am missing? Is there a way to programmatically do this?

Answer 1

The following macro will remove all non-printable characters and leading and trailing spaces using 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 to find replace: to search, press alt and 010 at the same time (on the keyboard 10), and then replace with a space.

You can do this as a substitute for mass by simply selecting cells containing breaks in the carriage.

Answer 3

I tried

 ws.Cells(i, j) = Replace(ws.Cells(i, j), Chr(13), "") 

and succeed.