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.
Remove all extra spaces including non-breaking spaces by replacing them with regular spaces
Get rid of all extra spaces including non-breaking spaces using an amazing tool
Remove all extra spaces including non-breaking spaces by replacing them with regular spaces
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).
Get rid of all extra spaces including non-breaking spaces using an amazing tool
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!
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.
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Other Operations (Articles)
How To Quickly Insert A Line Based On Current Date In Excel?
Supposing there is a sheet containing a row with dates, and now I want to insert a line right to the current date which will auto change while opening the workbook every day. Is there a trick can solve it in Excel?
How To Always Insert A Blank Row Below Instead Of Above In Excel?
As we all known, when inserting blank row in a worksheet, the blank row will always be inserted above the selected row or cell. But, sometimes, you may need to insert the row below the selected cell or row. How could you solve this job in Excel?
How To Insert A Blank New Row Automatically By Command Button In Excel?
In many cases, you may need to insert a blank new row into a specified position of your worksheet. In this article, we will show you how to insert a blank new row automatically by clicking on a Command Button in Excel.
How To Insert A Blank Row After Specific Text In Excel?
If you want to insert blank rows after a specific text as following screenshot shown, how to deal with it quickly and easily without inserting them manually one by one?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in