Trimming spaces

Question

I'm trying to parse left and right empty spaces, nbsp, \n, \t, etc. from all Excel cells in a certain range.

I'm using the following macro:

Sub TRIM_CELLS()

    'Clean all conditional formating
    Cells.FormatConditions.Delete

    'improve performance
    Application.ScreenUpdating = False

    Dim all_cells_range As String
    all_cells_range = "A1:A10"

    'Trim all those cells
    Range(all_cells_range).Select
    For Each cell In Selection.Cells
        cell.Value = Application.Substitute(Application.Substitute(CStr(cell.Value), vbLf, vbCr), vbCr, "")
        cell = WorksheetFunction.Trim((Application.Clean(Replace(cell.Value, Chr(160), " "))))
    Next cell
End Sub

Something like "Maria Tavares " doesn't get trimmed properly.

Try the solutions here - https://stackoverflow.com/q/32617535/2570277 — Nick, Oct 15 '19 at 13:04

Nick · Answer 1 · 2019-10-15T13:36:33.480

I use a function that removes any special characters that you define.

Function RemoveSpecialCharacters(wks As Worksheet, strRange As String, var As Variant)
Dim rngAddress As Range, cell As Range, I&

'e.g strRange - "E2:E"

With wks
Set rngAddress = .Range(strRange & .Cells(Rows.count, "A").End(xlUp).row)

  For I = LBound(var) To UBound(var)
    For Each cell In rngAddress
            cell = WorksheetFunction.Substitute(cell, var(I), " ")
                Else
            cell = WorksheetFunction.Substitute(cell, var(I), "")
    Next cell
  Next I
End With

End Function

You could call the function like this:

RemoveSpecialCharacters worksheetname, "A1:A", Array(Chr(9), Chr(10), Chr(13), Chr(39))

Where Chr(10) is linefeed character, chr(9) is the tab character etc.

See this link for what other Chr codes stand for.

UPDATE:

Try this to remove the weird "spy" character from the cell.

RemoveSpecialCharacters worksheetname, "A1:A", Array(Chr(160))

score 1 · Answer 2 · answered Oct 15 '19 at 11:06

1

@Nick: I tried to use your idea and I think the problem is the char itself... Assuming the following loop works as expected I would get the char that is causing the problem.

Take a look at this image:

But nothing gets printed in that place.

Sub TRIM_CELLS()

    'Clean all conditional formating
    Cells.FormatConditions.Delete

    'improve performance
    Application.ScreenUpdating = False

    Dim all_cells_range As String
    all_cells_range = "A1:A2"

    'Trim all those cells
    Range(all_cells_range).Select
    For Each cell In Selection.Cells
        For I = 1 To 255
            cell = WorksheetFunction.Substitute(cell, Chr(I), I)
        Next I

    Next cell
End Sub

answered Oct 15 '19 at 11:06

Eunito

416
5
22

what do you get if you type this in the immediate window `len(trim(cell))` ? – Nick Oct 15 '19 at 11:57
The "weird char/space" gets accounted! – Eunito Oct 15 '19 at 12:45
Try this. Convert the font in that cell to webdings. Type - not copy/paste - Maria Antonieta below it and also convert that to webdings. The rogue character should be visible at the end.What is it? – Nick Oct 15 '19 at 12:57
@Nick http://prntscr.com/pjl39z also did =code(cellWithOnlyWeirdChar) and the result was 160... but char(160) isn't converted – Eunito Oct 15 '19 at 13:28
see the update in my answer. it think it's a `chr(160)` – Nick Oct 15 '19 at 13:37
not working :( cell = WorksheetFunction.Substitute(cell, Chr(160), "----") – Eunito Oct 15 '19 at 13:45

score 0 · Answer 3 · answered Oct 15 '19 at 17:12

Ended up doing a replacement with that "space"... not a great solution but fixed my problem... Just would like to know what char it was...

Sub TRIM_CELLS()

'Clean all conditional formating
Cells.FormatConditions.Delete

'improve performance
Application.ScreenUpdating = False

Dim all_cells_range As String
all_cells_range = "A1:A2"
Range(all_cells_range).Select
For Each cell In Selection.Cells
    cell.Replace What:=" ", Replacement:=" ", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    cell.Value = Application.Substitute(Application.Substitute(CStr(cell.Value), vbLf, vbCr), vbCr, "")
    cell.Value = WorksheetFunction.Trim(cell)
Next cell

end sub

Trimming spaces

3 Answers3