Excel Trim Function Doesn’t Work

On many occasions, Excel trim function doesn’t work when trying to trim numbers pasted into Excel. It can be very frustrating to manually delete all empty spaces from every cell you pasted. Therefore I prepared a little macro to save you from this trouble.

As to why excel trim function doesn’t work as you expect and trim all spaces:

There are two kinds of spaces on ascii code system.

Regular spaces (ascii code: 32) and non-breaking spaces (ascii code 160).

Trim function can only remove regular spaces with asci code 32. So if you are unable to remove spaces with trim function, you are most likely dealing with non-breaking spaces.

If you copy this table and paste it into excel, you will see that trim function doesn’t remove spaces.

	2016	2017	2018
Sales	166.978	193.364	167.950
Delivery Costs	4.813	5.369	4.727
% to Sales	2,88	2,77	2,81

 

You can achieve desired trim functionality by using a substitute function inside trim function like shown below (formula will trim cell A1):

=TRIM(SUBSTITUTE(A1,CHAR(160),CHAR(32)))

This function changes non-breaking spaces to regular spaces first, then trim values.

Here is some info about non-breaking space: https://en.wikipedia.org/wiki/Non-breaking_space

---

Now it is time for making Excel trim everything for you:

Note: I don’t own most of the code in this macro, I basically found codes working for my problem and added them together. I found a macro that trims unwanted spaces here (anlong with many other useful code): http://www.mvps.org/dmcritchie/excel/join.htm#trimall and added some code after it to format trimmed cells as numbers.

Open your macro editor by using Alt+F11 shortcut or visual basic button on developer tab. If you don’t have developer tab enabled, here is how to do it: Enable Developer Tab.

Inset a Module from Insert menu and paste the code below.

Sub TrimALL()
 'David McRitchie 2000-07-03 mod 2000-08-16 2005-09-29 join.htm
 '-- http://www.mvps.org/dmcritchie/excel/join.htm#trimall
 ' - Optionally reenable improperly terminated Change Event macros
 Application.DisplayAlerts = True
 Application.EnableEvents = True 'should be part of Change Event macro
 If Application.Calculation = xlCalculationManual Then
 MsgBox "Calculation was OFF will be turned ON upon completion"
 End If
 Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual
 Dim cell As Range
 'Also Treat CHR 0160, as a space (CHR 032)
 Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
 LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
 'Trim in Excel removes extra internal spaces, VBA does not
 On Error Resume Next 'in case no text cells in selection
 For Each cell In Intersect(Selection, _
 Selection.SpecialCells(xlConstants, xlTextValues))
 cell.Value = Application.Trim(cell.Value)
 Next cell
 On Error GoTo 0
 Application.Calculation = xlCalculationAutomatic
 Application.ScreenUpdating = True

For Each xcell In Selection
 On Error Resume Next
 If xcell.Value <> vbNullString Then

xcell.Value = CDec(xcell.Value)

End If

Next xcell
 End Sub

If you select the table you pasted and run this code, you will see that all numbers on the table will be trimmed and formatted as numbers. Meanwhile text values will be untouched.