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.