6

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?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Parseltongue
  • 11,157
  • 30
  • 95
  • 160
  • Have you seen [THIS?](http://stackoverflow.com/questions/16219362/how-do-i-remove-special-spaces-in-excel-columns) – Siddharth Rout Apr 26 '13 at 22:25
  • I did take a look at that, although the solutions to not resolve the issue – Parseltongue Apr 26 '13 at 22:31
  • 1
    Did you try the Alt160 trick? – Siddharth Rout Apr 26 '13 at 22:32
  • 4
    What is the code for the last character which you're having a problem deleting? You can loop through the characters and `debug.print` the codes for each one: that should help with an approach to removing them. – Tim Williams Apr 26 '13 at 22:37
  • what Tim said. Sometimes the special characters look like something they are not, or even look like they are nothing at all. If you identify exactly what character(s) are offending, then you should be able to remove them! – David Zemens Apr 27 '13 at 01:29
  • Can you share a sample with us? – glh Apr 28 '13 at 02:30
  • Is this for cells themselves and using `=clean(..)` wont work because it uses another cell? – glh Apr 28 '13 at 03:24
  • Seen this> [How to remove leading or trailing spaces in an entire column of excel worksheet in Excel2010](http://stackoverflow.com/questions/9578397/how-to-remove-leading-or-trailing-spaces-in-an-entire-column-of-excel-worksheet) – brettdj Apr 28 '13 at 05:15

3 Answers3

15

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
glh
  • 4,900
  • 3
  • 23
  • 40
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.

Angela
  • 21
  • 1
1

I have tried 

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

and succeed.