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

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

(Note: the video may not reflect the latest upgrades. Please use the guide below.)

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 trim spaces, line breaks, nonprinting characters in Excel

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
    • UPPER CASE
  3. Click Apply Done, text case is unified throughout the range.
How to change text case in Excel cells

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 change text to number format in Excel

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.
How to change numbers to text format in Excel
Any questions or suggestions?
close window
How can we help?