Why Is TRIM Not Removing Spaces In Excel?

Are you trying to clean a dataset with TRIM but somehow you can’t get rid of that blank space? Yes, it’s super annoying! Does the below issue in cell B2 look familiar and do you need your data to look like cell B1?

Example TRIM issue

Manually cleaning the data should always be the last resort, so check out these three extra tips before you start.

TRIM Not Working? Try These Tips To Remove Spaces In Excel

If you’ve come across this page, I’m assuming you have already tried the TRIM function. Try testing these three techniques as well.

Substitute Function (and TRIM)

The reason why TRIM doesn’t work on certain spaces is that they are non-breaking spaces. The SUBSTITUTE function can help remove these types of spaces. You can throw in a final TRIM on top of all the formulas to remove any rogue spaces!

To confirm if it is indeed a non-breaking space, you can use:

  • =CODE() – This returns the numeric figure for a character that’s recognized by the computer
  • Pair CODE with LEFT() or RIGHT() based on the position of your erroneous space

Based on our example earlier – I would write a function like this:

CODE function

Note – I’m currently writing this on a Mac so the returning figure is 202. If you’re doing this on a PC, it will return 160.

Now to bring it all together. To remove the spaces before the “?” – I would use this formula:

  • =SUBSTITUTE(cell reference to your text, CHAR(202), “”)
  • Instead of 202, put in the figure you determined in the =CODE() step, likely 160
Final SUBSTITUTE formula

Find & Replace

Another way you can try removing the blank spaces is by using Excel’s find and replace function.

  1. Highlight the trouble columns or cells – I wouldn’t blanket run this over your Excel file as this might mess up other issues
  2. Ctrl + H to open the “Find and Replace” dialog box. Ctrl +F and clicking on the Replace tab does the job as well
  3. Press SPACE in the “Find what:” box and leave the “Replace with” blank
  4. Click on “Replace all” and then click OK

This method is also handy for removing double spaces. Just replace the space in “Find what:” with a double space!

Related article – How To Manipulate Text Data In Excel

VBA

If you’re constantly importing data sets that are giving you issues, I suggest setting up a VBA macro. This will help automate the removal of all leading/trailing spaces. This will also remove/convert CHAR(160) spaces into a standard space.

The original source for the code with additional explanations. Really helpful that the original coder commented on all the steps!

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
End Sub

You can find out more on how to add a macro to Excel via Microsoft’s tutorial.

Thanks for reading and I hope you found this helpful.

Other Excel tutorials you may also like:

Leave a Comment