Clean cells from extra spaces, line breaks, change text case, convert cell format from text to number and more

(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, non-printing characters, inconsistent text case… That's inevitable for everyone who has ever imported data into Excel or combined multiple workbooks. So how can you clean large tables quickly, when manually deleting or copying Excel functions are not productive options?

The Data Cleaning add-in helps clean massive data sets in seconds:

  • Trim leading, trailing and all excess spaces
  • Remove line breaks and non-printing characters
  • Change text case in an entire range
  • Convert numbers stored as text to proper numbers
  • Convert cell format from number to text

Before you begin, add Data Cleaning to Excel

Data Cleaning is one of the 20+ features within XLTools Add-in for Excel. Works in Excel 2019, 2016, 2013, 2010, desktop Office 365.

– free trial gives you 14 days of full access to all features.

How to trim excess spaces in cells

  1. Click theData Cleaningbutton on XLTools ribbon Select the range.
  2. Specify what to clean from:

    • Leading and trailing spaces
    • Excess spaces
  3. Click Apply Done, redundant spaces are deleted.

How to remove line breaks in cells

  1. Click theData Cleaningbutton on XLTools ribbon Select the range.
  2. Choose to clean fromLine breaks.
  3. Click Apply Done, any line breaks (or carriage returns, or new lines) are removed.

How to remove non-printing characters in cells

  1. Click theData Cleaningbutton on XLTools ribbon Select the range.
  2. Choose to clean fromNon-printing characters.
  3. Click Apply Done, nonprintable characters are deleted.

How to change text case throughout a range

  1. Click theData Cleaningbutton on XLTools ribbon Select the range.
  2. SelectChange characters caseand choose one of the letter capitalization options:

    • Proper Case
    • Sentence case
    • lower case
  3. Click Apply Done, text case is unified throughout the range.

How to convert numbers stored as text to proper numbers

When a cell with a numeric value has an error indicator in the upper-left corner – it means the number is actually stored as text. So Excel will treat it as text and ignore in calculations. That is why it's critical to convert such unrecognized number into to proper number format.

  1. Click theData Cleaningbutton on XLTools ribbon Select the range.
  2. SelectText to number.
  3. Click Apply Done, each cell is formatted as number and loses the error indicator.

How to convert numbers to text format

When you want numeric values in cells to be treated as text, you can do this by adding an apostrophe (a single quote) in front of the value. This will effectively convert the value to text format.

  1. Click theData Cleaningbutton on XLTools ribbon Select the range.
  2. SelectNumber to Text.
  3. Click Apply Done, each cell is formatted as text. Note that the cells get an indicator in the upper-left corner to remind you that the numeric value is actually stored as text.
Any questions or suggestions?
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 11: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 16:48
DmitryHi! How can I cancel the conversion of cells from uppercase to lowercase?
April 19, 2019 at 14: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 15:27
DmitryThank you Maria, I've returned lowercase by applying this add-in. Appreciate your help. All best!
April 19, 2019 at 17: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 18:55
Maria BalobanovaHello Steve, I am guessing, you are talking about thisInquire 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 fromMS Support.
April 10, 2017 at 16:03
Den JoDoes it support the version on Mac iOS?
December 14, 2016 at 16: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 14: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 18:58
Maria BalobanovaHello Susan, thank you for contacting us! 1.Combine Cells Datafeature 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 tryXLTools Version Control, that allows you to save history of a workbook.
October 11, 2016 at 15:04
joshua venablecan this be included in the automation?
July 20, 2016 at 00: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 16:20
TylerUnfortunately the whole "change text case"section doesn't work. Bummer. Would have been really useful.
February 4, 2016 at 07: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 15:13
David GhikasDownloaded XLTools and it worked like a charm. Very sharp.
October 15, 2015 at 22: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 14:31
OGWANG Ambrosei need to know more about cleaning with a pivot table
August 6, 2015 at 11: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 16:18
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 20:33
How can we help?