Clean cells from excess spaces, line breaks and non-printable characters.
(Note: the video may not reflect the latest upgrades. Please use the guide below.)
How much time do you spend scrubbing and cleansing data before you actually begin spreadsheet analysis? Redundant spaces and non-printable characters are almost inevitable for everyone who has ever imported data to Excel from the web or from external applications. Manually finding and deleting unwanted characters, or using Excel functions is not productive when you work with large tables.
The Clean Cells feature add-in helps clean a data set in seconds:
- Trim leading, trailing and all excess spaces between words
- Remove non-breaking "&nbps" spaces
- Remove line breaks and other non-printable characters
Before you begin, add Clean Cells to Excel
Clean Cells is one of the 20+ features within XLTools Add-in for Excel. Works in Excel 2024, 2019, 2016, 2013, 2010, and Microsoft 365.
How to trim leading and trailing spaces in cells
Sometimes cells may contain redundant spaces at the beginning or the end of a text string. Somewhat similar to =TRIM function in Excel, you can remove these unwanted spaces, but all at once and throughout the entire range.
- Click the Data Cleaning button on XLTools ribbon Select Clean cells from the drop-down list A dialogue box will open.
- Select the range which you want to clean.Tip:Click on any cell in a table, and the entire table will be automatically selected.
- Check the box leading and trailing spaces.
- Click OK Done, the unwanted leading and trailing spaces are removed.
Select 
How to remove all excess spaces between words
When cells contain redundant spaces between words, you may want to cut the multiple spaces down to a single space. Somewhat similar to Excel's =TRIM function, you can remove these excess spaces, but all at once and throughout the entire range.
When you remove excess spaces, the Clean Cells feature will remove all leading and trailing spaces at the same time.
- Click the Data Cleaning button on XLTools ribbon Select Clean cells from the drop-down list A dialogue box will open.
- Select the range which you want to clean.Tip:Click on any cell in a table, and the entire table will be automatically selected.
- Check the box all excess spaces.
- Click OK Done, the excess spaces are trimmed down to a single space.
How to remove non-breaking "&nbps" spaces from cells
Non-breaking "&nbps" spaces are html characters that may appear in a spreadsheet if you copy or import data from the web or external applications. Usually "&nbps" spaces look just like the standard space character, but =TRIM function in Excel cannot identify them and processes only standard spaces (7-bit ASCII code, value 32).
The Clean Cells feature will remove non-breaking "&nbps" spaces (Unicode, value 160) from the entire range.
- Click the Data Cleaning button on XLTools ribbon Select Clean cells from the drop-down list A dialogue box will open.
- Select the range which you want to clean.Tip:Click on any cell in a table, and the entire table will be automatically selected.
- Check the box non-breaking "&nbps" spaces.
- Click OK Done, all non-breaking "&nbps" spaces are removed.
How to delete line breaks from cells
Line break is one of the non-printable characters that may appear in a spreadsheet if you use Alt+Enter, or if you copy or import data from the web or external applications. Somewhat similar to Excel's =CLEAN function, you can delete line breaks, but all at once and throughout the entire range.
The Clean Cells feature will remove line breaks (CR, ASCII code 13) and line feed (LF, ASCII code 10) characters from cells.
- Click the Data Cleaning button on XLTools ribbon Select Clean cells from the drop-down list A dialogue box will open.
- Select the range which you want to clean.Tip:Click on any cell in a table, and the entire table will be automatically selected.
- Check the box line breaks.
- Click OK Done, all line breaks in cells are removed.
How to clean cells from non-printable characters
Non-printable characters are control characters for content formatting, for example, tab , pilcrow ¶, line break ↵, or non-printing spaces. These characters may appear in a spreadsheet if you copy or import data from the web or external applications.
Instead of combining Excel functions =CLEAN, =CODE, and =SUBSTITUTE, you can clean the entire range at once.
The Clean Cells feature will remove the following non-printable characters:
- Non-printable characters in 7-bit ASCII code (values 1 through 31)
- Unicode non-printable characters (values 127, 129, 141, 143, 144, 157, 160)
- Click the Data Cleaning button on XLTools ribbon Select Clean cells from the drop-down list A dialogue box will open.
- Select the range which you want to clean.Tip:Click on any cell in a table, and the entire table will be automatically selected.
- Check the box non-printable characters.
- Click OK Done, all non-printable characters in cells are removed.
How can I cancel the conversion of cells from uppercase to lowercase?
If you understand you correctly: you have converted uppercase text in cells to lowercase and now you want to reverse the operation?
Excel has a limitation for the Undo functionality when it comes to add-ins. But I suggest you select the range of cells and then use Data Cleaning to convert the text from lowercase back to uppercase.
Thank you Maria,
I've returned lowercase by applying this add-in. Appreciate your help.
All best!
I am guessing, you are talking about this Inquire Add-in. This add-in is developed by Microsoft and is only available in Office Professional Plus 2013 or Office Professional Plus 2016.
We are considering to add similar functionality to our XLTools add-in in the next versions.
You also try cleaning all excess formatting manually (before you try, please save a backup copy of your original file):
1. Select all columns to the right of the last column in your spreadsheet. Or simply type F5 > type the reference range, e.g. F:IV > Home tab > Editing group > Click "Clear all".
2. Select all rows below of the last row in your spreadsheet. Or type F5 > type the reference range, e.g. 5:65536 > Home tab > Editing group > Click "Clear all".
More tips from MS Support.
XLTools desktop version is only supported for Windows.
But we have a Data Cleansing feature in the XLTools add-in for Windows.
1. Combine Cells Data feature will add a separator of your choice between (comma, comma+space, etc.) the contents of the combined cells.
I guess you want to combine 3 cells: ‘Barns’, ‘blank cell’, ‘Lisa’ > So you get two commas because of a blank cell: ‘Barnes, , Lisa’
To disregard empty cells, please tick the box “Skip Empty cells” > You will get ‘Barnes, Lisa’
2. XLTools is a COM type of add-in. The drawback of all add-ins of this type is that Excel does not allow UNDO on their operations. Note, you can still UNDO any standard Excel operations, but not the add-in's operations. The best way around it is to back up your spreadsheet and keep the original just in case. Or try XLTools Version Control, that allows you to save history of a workbook.