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.

Get started with XLTools ribbon
– free trial gives you 14 days of full access to all features.

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.

  1. Click the Data Cleaning button on XLTools ribbon Select Clean cells from the drop-down list A dialogue box will open.

  2. Select the range which you want to clean

    Tip: click on any cell in a table, and the entire table will be automatically selected.

  3. Check the box leading and trailing spaces.

    Select the range to clean from leading and trailing spaces
  4. Click OK Done, the unwanted leading and trailing spaces are removed.

    See result: leading and trailing spaces are removed

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.

  1. Click the Data Cleaning button on XLTools ribbon Select Clean cells from the drop-down list A dialogue box will open.

  2. Select the range which you want to clean.

    Tip: click on any cell in a table, and the entire table will be automatically selected.

  3. Check the box all excess spaces.

    Select the range to clean from excess spaces
  4. 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.

  1. Click the Data Cleaning button on XLTools ribbon Select Clean cells from the drop-down list A dialogue box will open.

  2. Select the range which you want to clean.

    Tip: click on any cell in a table, and the entire table will be automatically selected.

  3. Check the box non-breaking “&nbps” spaces.

    Select the range to clean from non-breaking spaces
  4. 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.

  1. Click the Data Cleaning button on XLTools ribbon Select Clean cells from the drop-down list A dialogue box will open.

  2. Select the range which you want to clean.

    Tip: click on any cell in a table, and the entire table will be automatically selected.

  3. Check the box line breaks.

    Select the range to clean from line breaks
  4. 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)

  1. Click the Data Cleaning button on XLTools ribbon Select Clean cells from the drop-down list A dialogue box will open.

  2. Select the range which you want to clean.

    Tip: click on any cell in a table, and the entire table will be automatically selected.

  3. Check the box non-printable characters.

    Select the range to clean from non-printable characters
  4. 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.
July 15, 2015 at 16:33
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
September 26, 2019 at 08:10
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!
September 26, 2019 at 13:48
Dmitry Hi! How can I cancel the conversion of cells from uppercase to lowercase?
April 19, 2019 at 10:57
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.
April 19, 2019 at 11:27
Dmitry Thank you Maria, I've returned lowercase by applying this add-in. Appreciate your help. All best!
April 19, 2019 at 13:04
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?
April 9, 2017 at 14:55
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.
April 10, 2017 at 12:03
Den JoDoes it support the version on Mac iOS?
December 14, 2016 at 12:53
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.
December 15, 2016 at 10:33
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!
October 10, 2016 at 14:58
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.
October 11, 2016 at 11:04
joshua venablecan this be included in the automation?
July 19, 2016 at 20:00
Maria BalobanovaHi Joshua, not yet. That's a very good idea, thank you. Data Cleaning Automation will be supported in the next releases.
July 26, 2016 at 12:20
TylerUnfortunately the whole "change text case"section doesn't work. Bummer. Would have been really useful.
February 4, 2016 at 03:09
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.
February 4, 2016 at 11:13
David GhikasDownloaded XLTools and it worked like a charm. Very sharp.
October 15, 2015 at 18:01
Maria BalobanovaThank you, David! In a few days Data Cleaning will also be available (and free) as an App in the Microsoft Office Store.
October 16, 2015 at 10:31
OGWANG Ambrosei need to know more about cleaning with a pivot table
August 6, 2015 at 07:35
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.
August 6, 2015 at 12:18
How can we help?