What to do when Excel’s TRIM function is unable to remove spaces
If the TRIM function is not working as you expected and there are still spaces in your cell, depending on your operating system, you can use one of our formulas that remove non-breaking spaces in Excel for Windows or one of our formulas that remove non-breaking spaces in Excel for Mac.
If the length of a string is longer than the number of visible characters, then the string contains space(s). In Excel spaces are not blank areas but characters and each character in Excel contains a numerical code that is based on the operating systems character set (the code is usually the same as the ASCII code).
There are 2 types of spaces:
A normal space: this has a code of 32 in Excel for Mac and Excel for Windows. A normal space appears when you press the enter key on your keyboard.
A non-breaking space: this has a code of 160 in Excel for Windows and a code of 202 in Excel for Mac. A non-breaking space appears in Excel for Windows when you hold the alt key and enter 0160 using the numpad (this will not work if you use the numbers under the function keys). A non-breaking space appears in Excel for Mac when you press option/alt + enter on your keyboard.
When you want to remove unwanted spaces from a cell your first instinct will probably be to use the TRIM function, however the TRIM function will only work on regular spaces. If the cell contains non-breaking spaces the TRIM function will not be able to remove the spaces.
Formula to check if a cell contains non-breaking spaces in Excel for Windows
You can use the following formula in Excel for Windows to check if a cell contains a non-breaking space:
=SEARCH(CHAR(160),A2)
Formula to check if a cell contains non-breaking spaces in Excel for Mac
You can use the following formula in Excel for Mac to check if a cell contains a non-breaking space:
=SEARCH(CHAR(202),A2)
A breakdown of the formulas which check to see if a cell contains non-breaking spaces
The above formulae use the SEARCH function to locate the position of the first non-breaking space in cell A2. Please note that if the cell contains more than one non-breaking space the formula will only give the position of the first non-breaking space. In addition, the formula will return a #VALUE error if the cell does not contain a non-breaking space.
How to minimise the risk of non-breaking spaces appearing in your Excel file
Non-Breaking Spaces are common character entities in HTML. When data from a HTML source is pasted into Excel the default paste setting is HTML. If you are pasting data into Excel from a HTML source, we recommend using the paste special option to paste the data as text or Unicode text.
To bring up the paste special dialog box, you can click on the Paste icon in the Home tab and then click on Paste Special or you can press Ctrl + Alt + V on your keyboard.
These four formulas remove non-breaking spaces from cells in Excel for Windows
We recommend using Excel’s paste special option to paste the formula as text or Unicode text.
=TRIM(SUBSTITUTE(A2,CHAR(160),""))
=TRIM(SUBSTITUTE(A2,CHAR(160),CHAR(32)))
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160),CHAR(32))))
The above formulas remove all non-breaking spaces from cell A2 in Excel for Windows. N.B. in Excel for Mac, CHAR (160) does not map to the non-breaking space character, but to the "dagger" character (†).
These four formulas remove non-breaking spaces from cells in Excel for Mac
We recommend using Excel’s paste special option to paste the formula as text or Unicode text.
=TRIM(SUBSTITUTE(A2,CHAR(202),""))
=TRIM(SUBSTITUTE(A2,CHAR(202),CHAR(32)))
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(202)," ")))
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(202),CHAR(32))))
The above formulas remove all non-breaking spaces from cell A2 in Excel for Mac. N.B. In Excel for Mac, non-print characters cannot be seen in the cell, however some non-print characters can be seen in the formula bar.
A breakdown of the formulas which remove non-breaking spaces from a cell
The CHAR function takes the numerical code of a character and displays the character. So in our formulas, CHAR(32) represents a normal space and CHAR(160) represents a non-breaking space in Excel for Windows and CHAR(202) represents a non-breaking space in Excel for Mac.
A normal space can also be represented in a formula by placing a space within quotation marks (this is used in some of our formulas).
The SUBSTITUTE function replaces all non-breaking spaces in the cell with normal spaces so the TRIM function can remove the spaces from the cell.
The CLEAN function removes all non-printable characters and line breaks from the cell.
The TRIM function removes all extra normal spaces from the cell so that there are no spaces at the beginning and end of the cell and only a single space between words in the cell.
This User Defined Function removes non-breaking spaces in Excel for Windows
The VBA code below creates a User Defined Function called TrimNBSP which removes non-breaking spaces from a cell in Excel for Windows.
Public Function TrimNBSP(ByVal value As String) As String TrimNBSP = Trim(Replace(value, Chr(160), Chr(32))) End Function
