How to remove all extra spaces including non-breaking spaces in Excel?
Sometimes we may find extra spaces in the text a headache. They mess up our formulas and make our data difficult to read. Usually, we can use the TRIM function in Excel to remove those extra spaces. But when it comes to non-breaking spaces, the TRIM function just fails to do the job. The reason is that the TRIM function can only remove the space character that has value 32 in the 7-bit ASCII system, while a non-breaking space character has value 160 in the 7-bit ASCII. Therefore, how we can remove all extra spaces including non-breaking spaces in Excel? This tutorial will show you two quick ways to get the job done.
To clear away the extra regular spaces and non-breaking spaces, we can use the help of the TRIM function and the SUBSTITUTE function. Please do as follows.
1. To remove the extra spaces as well as non-breaking spaces in cell A2, please copy the formula below into cell C2, then press the Enter key to get the result.
=TRIM(SUBSTITUTE(A2, UNICHAR(160), " "))
2. Select this result cell and drag its autofill handle down to get the rest of the results.
Note: In the formula above, we can change the UNICHAR function to the CHAR function. UNICHAR is the extension of CHAR, and it was introduced in 2013 in Excel. The formula can become:
=TRIM(SUBSTITUTE(A2, CHAR(160), " "))
But in some versions of Excel, CHAR(160) may not work well. So when CHAR(160) doesn’t function, please change it to UNICHAR(160).
In addition to the above method, the Remove Spaces feature of Kutools for Excel can help you clear away all extra spaces including non-breaking spaces in just a few clicks!
Before applying Kutools for Excel, please download and install it firstly.
1. Select data range you want the remove the spaces.
2. Go to Kutools tab, and click Text > Remove Spaces.
3. The Remove Spaces dialog box pops up. Check the All excess spaces option, and click the OK button.
Now all the extra regular spaces and non-breaking spaces are removed.