Skip to main content

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.
doc remove-extra-spaces-non-breaking 1

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


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), " "))

doc remove-extra-spaces-non-breaking 2

2. Select this result cell and drag its autofill handle down to get the rest of the results.
doc remove-extra-spaces-non-breaking 3

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.
doc remove-extra-spaces-non-breaking 4

3. The Remove Spaces dialog box pops up. Check the All excess spaces option, and click the OK button.
doc remove-extra-spaces-non-breaking 5

Now all the extra regular spaces and non-breaking spaces are removed.
doc remove-extra-spaces-non-breaking 6


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?


  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
kte tab 201905
  • 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!
officetab bottom
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations