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 2019, 2016, 2013, 2010, desktop Office 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.
![Select the range to clean from leading and trailing spaces]()
- Click OK Done, the unwanted leading and trailing spaces are removed.
![See result: 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.
![Select the range to clean from excess spaces]()
- Click OK Done, the excess spaces are trimmed down to a single space.
![See result: all excess spaces are removed]()
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.
![Select the range to clean from non-breaking spaces]()
- Click OK Done, all non-breaking “&nbps” spaces are removed.
![See result: all non-breaking 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.
![Select the range to clean from line breaks]()
- Click OK Done, all line breaks in cells are removed.
![See result: all line breaks 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.
![Select the range to clean from non-printable characters]()
- Click OK Done, all non-printable characters in cells are removed.
![See result: all non-printable characters are removed]()
Any questions or suggestions?
Oscar TongThis is a very helpful tip. I've never really known what to do with that button, being rather new to excel myself.
Rick DIs there an add in that changes case (like proper) but maintains post nominal letters, address nuances (like NW, PO ) and upper case in middle of name (McDonald) or lower case in front (vonSickle). Pls help I'm going blind making all the corrections. ofc 365 pro
Maria BalobanovaI'm so sorry you have to go through that! At the moment, XLTools does not (yet) have this functionality. But that is an excellent suggestion for future releases. Thank you!
Dmitry Hi! How can I cancel the conversion of cells from uppercase to lowercase?
Maria BalobanovaHi Dmitry, 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.
Dmitry Thank you Maria, I've returned lowercase by applying this add-in. Appreciate your help. All best!
Steve KossorI want to use the Excess Format Cleaner in Excel 2016 like I used to in 2013 but it doesn't seem to have the feature of "lopping off" unused rows/columns like the older version did. How can I lop off unnecessary parts of a spreadsheet now?
Maria BalobanovaHello Steve, 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.
Den JoDoes it support the version on Mac iOS?
Maria BalobanovaHello Den Jo, XLTools desktop version is only supported for Windows. But we have another tool - Data Cleaning Office add-in. It also works on Excel for iPad, Excel for Mac and is available in the Office Store. Note, that functionality is a bit different.
SusanWhen I combine data with a separator selected, such as a comma, it always adds to commas there and makes the data like ' Barnes,, Lisa'. Is there a way to add just one comma there? And, is there way to 'undo' or 'go back' like we use often in Excel and Word? The undo button is inactive when the XLtools is active. Thanks!
Maria BalobanovaHello Susan, thank you for contacting us! 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.
joshua venablecan this be included in the automation?
Maria BalobanovaHi Joshua, not yet. That's a very good idea, thank you. Data Cleaning Automation will be supported in the next releases.
TylerUnfortunately the whole "change text case"section doesn't work. Bummer. Would have been really useful.
Maria BalobanovaHello Tyler, I have tested it this option in Data Cleaning just now - seems to work fine. I will send you an email, so we could figure out why it's not working on your PC.
David GhikasDownloaded XLTools and it worked like a charm. Very sharp.
Maria BalobanovaThank you, David! In a few days Data Cleaning will also be available (and free) as an App in the Microsoft Office Store.
OGWANG Ambrosei need to know more about cleaning with a pivot table
Maria BalobanovaOgwang, Data Cleaning does not work with Excel generated pivot tables - yet. You can apply this feature and clean your original/primary data first, and then create a PivotTable report.
