CODEWITHSUNDEEP
excelvba
Parseltongue
Parseltongue

Reputation: 11697

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

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?

Upvotes: 6

Views: 51855

Answers (4)

santhosha
santhosha

Reputation: 439

  • Step 1: Select the entire column
    step 2: Press Cntrl+ H
    Step 3: Type'Cntrl+J' in the Find what box
    Step 4: Hit 'replace all'

Note: Cntrl+J here captures the carriage return cells.

Upvotes: 0

user4087042
user4087042

Reputation:

I have tried 

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

and succeed.

Upvotes: 1

Angela
Angela

Reputation: 21

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.

Upvotes: 2

glh
glh

Reputation: 4972

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

Upvotes: 15

Related Questions