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?
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:
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
Find & Replace
Another way you can try removing the blank spaces is by using Excel’s find and replace function.
- Highlight the trouble columns or cells – I wouldn’t blanket run this over your Excel file as this might mess up other issues
- Ctrl + H to open the “Find and Replace” dialog box. Ctrl +F and clicking on the Replace tab does the job as well
- Press SPACE in the “Find what:” box and leave the “Replace with” blank
- 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
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: