Clean Data in Excel – Trim, Change Case, Convert

Excel Data Cleaning: Spaces, Line Breaks, Capitals and Text Numbers, All in One Pane

A spreadsheet comes in from a web shop, an accounting system or a colleague, and it looks fine. Then a VLOOKUP can’t find a name that is plainly on the screen, and a total comes out too low. The cause is usually something you can’t see: an extra space, a line break inside a cell, a number stored as text. Data Cleaning in Dose for Excel lets you clean data in Excel without formulas or helper columns. Select your cells, tick what you want fixed, and click Apply.

It works in place, it can keep a backup copy of your sheet first, and it leaves alone the things that should stay as they are, such as your formulas and order numbers like 00123.

Download Free Trial

Buy Now

▶ Watch Demo

Click Apply, say Yes to the backup, and the names are tidy, the amounts are real numbers and the lookup works.

Why Messy Data Causes So Much Trouble

Excel takes your data literally. To you, Maria Garcia and maria GARCIA are the same customer. To VLOOKUP or XLOOKUP, the extra spaces make them two different values, so you get #N/A. To you, 1,200.50 is an amount. If it arrived as text, SUM skips it without a word, and the total at the bottom of the column looks perfectly believable and is simply wrong.

Here is the sort of list that causes this. Every problem on this page comes from this one sheet:

Before you clean data in Excel: names with extra spaces and mixed capitals, a name split over two lines, amounts stored as text, a wrong total and a lookup showing #N/A — A customer export before cleaning. The total reads 6,580.00, and the lookup can’t find Maria Garcia.

What Excel’s TRIM, CLEAN and PROPER Leave Behind

Data cleaning in Excel usually starts with its own functions, and they are fine for one quick fix. But to clean data in Excel this way, each one needs a helper column, then copying and pasting the values back, then deleting the column. They also miss some of the commonest problems, as Microsoft’s own pages for these functions explain:

The problem	What Excel offers	What it misses
Extra spaces	TRIM	The invisible web space (the non-breaking space, character 160) that comes with text copied from web pages. It looks like a space, and TRIM doesn’t touch it.
Line breaks in a cell	CLEAN	It removes the break but puts nothing in its place, so two words on two lines become one: saralee
Characters that don’t print	CLEAN	It only knows the first 32 control characters. The zero-width characters that arrive with pasted web text stay.
Mixed capitals	UPPER, LOWER, PROPER	There is no sentence case function at all, and each one is another helper column.
Numbers stored as text	VALUE, or Convert to Number	They convert order numbers too, so 00123 becomes 123 and the leading zeros are gone.

What Data Cleaning Fixes

This data cleaning tool has three groups in one pane. Use any one of them, or all three together on the same cells.

Trim spaces and non-printing characters

All four options are ticked when the pane opens. Untick any you don’t want.

Option	What it does	Example
Trim spaces at the beginning/end of text	Removes spaces before and after the text, including invisible web spaces.	John becomes John
Remove all excess spaces	Leaves one space between words.	John Doe becomes John Doe
Remove line breaks in cells	Replaces each break with one space, so the words stay apart.	sara over lee becomes sara lee
Remove non-printing characters	Removes control characters and zero-width characters. A tab becomes a space.	emmawilson with a hidden tab becomes emma wilson

The last one is easiest to understand with a picture. The cell below looks as if the two names were typed together, but the formula bar shows a gap. That gap is a character the cell doesn’t display:

A cell showing emmawilson while the formula bar shows a gap between the names, and another cell with sara and lee on two lines because of a line break — A hidden character in *emmawilson*, shown by the formula bar, and a line break splitting *sara* and *lee* in row 4.

Change characters case to

Option	Example	Good for
UPPER CASE	sku-1042 becomes SKU-1042	Product and country references
lower case	Help@COMPANY.com becomes help@company.com	Email lists and web addresses
Proper Case	john SMITH becomes John Smith	Names and cities
Sentence case	WELCOME to OUR shop becomes Welcome to our shop	Descriptions and notes

Converting

Text to numbers turns amounts stored as text into real numbers that SUM and your charts can use: 1,200.50 becomes 1200.5, $1,200 becomes 1200, and (100) becomes -100. Number separators and the currency symbol follow your Windows regional settings.

Numbers to text does the reverse, and keeps what the cell shows. A number displayed as 01042, $12.50 or 12.5% becomes that same text, which is useful for zip numbers, account numbers and anything else you never want added up. You choose one direction or the other for each run.

How to Clean Data in Excel, Step by Step

To clean data in Excel, use the Data Cleaning button on the DOSE tab, marked here with a rectangle — The Data Cleaning button on the DOSE tab, in its own group.

Select the cells you want to clean. That can be a range, a whole column, several separate ranges, or the whole sheet.
Go to the DOSE tab and click Data Cleaning. The pane opens on the right.
Leave the options you want in Trim spaces and non-printing characters ticked.
Tick Change characters case to if the capitals need fixing, and choose a style.
Tick Converting if you have numbers stored as text, and choose Text to numbers.
Tick Backup sheet if you want a copy of the sheet first.
Click Apply. If you ticked Backup sheet, Dose asks Do you want to backup your sheet before? Click Yes for the copy. Your cells are cleaned quickly, and the pane stays open if you want another run.

Data Cleaning pane with the four trim options, Proper Case, Text to numbers and Backup sheet all ticked — The pane as it is set up in the example below.

A Worked Example: Cleaning a Customer Export

Back to the sheet from the top of this page. It has twelve orders, and these problems:

Extra spaces in names and cities, plus invisible web spaces you can’t spot by eye.
Line breaks in two names, and a hidden tab in another.
Capitals all over the place: JOHN SMITH, madrid, DUBAI.
Seven of the twelve amounts stored as text, some with a dollar sign and one in brackets. They sit on the left of the column, which is the visible clue.
So the Total reads 6,580.00, because SUM only counts the five real numbers.
And the lookup shows #N/A, because of the spaces in maria GARCIA.

In the example below we selected the table from A1 to E14, including the total. We kept all four trim options ticked, ticked Change characters case to with Proper Case, ticked Converting with Text to numbers, and ticked Backup sheet. Then we clicked Apply and Yes. The result:

After you clean data in Excel: tidy names and cities, every amount a real number, the total at 14,011.25 and the lookup finding 1,200.50 — After cleaning: tidy names and cities, real numbers, and a lookup that finds Maria Garcia. Order numbers still start with their zeros.

The total is now 14,011.25, a difference of more than 7,000 that nothing on the screen had warned about. It’s still the same formula, which is the point: nothing was wrong with the formula, only with what it was given.

Same formula, before and after: =SUM(E2:E13) goes from 6,580.00 to 14,011.25.

What Data Cleaning Leaves Alone

A cleaning tool you have to check afterwards isn’t much use. So when you clean data in Excel with Data Cleaning, a cell only changes when there is something to fix, and the tool steps around anything that could be damaged:

In your selection	What happens
Formulas	Left as they are. They stay formulas, and they update when the cells they read are cleaned.
Error values such as #N/A	Left as they are.
Order numbers and other values with leading zeros, such as 00123	Stay as text with Text to numbers, so the zeros are kept.
Numbers longer than 15 digits	Stay as text with Text to numbers, because Excel can’t hold them exactly as numbers.
Text that only looks a bit like a number, such as 1e5, 1,2,3 or 5%	Stays as text with Text to numbers.
Dates	Skipped by the case options and by Text to numbers. Numbers to text leaves them alone too.
Numbers, when you change case	Skipped, so a case change across a mixed column can’t disturb them.
Text with partly bold words	Untouched if it has nothing to clean. If its text does change, the partial bold is lost, because the cell gets its new text as a whole.

In the example above, the selection included the total in E14 on purpose. The formula is still there afterwards, and all twelve order numbers still begin with their zeros.

Clean Only the Rows You Can See

Sometimes only part of a list should change. Filter it first, and Data Cleaning works on the visible cells only. Hidden rows and columns stay exactly as they were.

In the example below we filtered the Status column to Open, selected the table with the same cleaning options, and clicked Apply. Then we cleared the filter. The open orders are clean, and the paid orders still have every flaw they started with:

Only the filtered rows change. Clearing the filter shows the paid orders as they were.

List filtered to open orders before cleaning, with messy names and amounts stored as text — Filtered to Open, before cleaning

After clearing the filter, the open orders are clean and the paid orders still have their extra spaces, capitals and text amounts — Filter cleared: Open rows clean, Paid rows unchanged

Keep a Backup of Your Sheet First

Before you clean data in Excel, it’s worth having a way back. Tick Backup sheet and Dose asks you before it starts. Click Yes, and it adds a full copy of the sheet right next to the original, named with a (2) at the end. Your cells are then cleaned on the original sheet, and the copy keeps everything as it was, so you can compare the two or go back to it. Click No, and it cleans without making a copy.

The Customer Export (2) backup sheet, still showing the messy data, the total of 6,580.00 and the lookup at #N/A — The backup sheet, Customer Export (2), with the original data still in it.

Good to Know Before You Run It

Proper Case capitalizes every word it finds. That suits names and cities, but O’NEIL becomes O’neil, help@company.com becomes Help@Company.Com, and a heading like Order ID becomes Order Id. Select only the columns that need it, and leave out emails, abbreviations and headings like that one.
A cell with nothing but spaces becomes empty. That is usually what you want, because those cells look blank but aren’t.
Protected sheets: if the sheet is protected, Dose asks you to unprotect it first, unless every cell you selected is unlocked.
Nothing to clean? If the cells you selected are all blank, Dose tells you to select cells with data.
Remember my settings: open the menu at the top of the pane to save your choices for next time, or to reset them.

When People Use Data Cleaning

These are the moments people most often need to clean data in Excel:

Exports from other systems: web shops, accounting packages and CRMs often send names padded with spaces and amounts as text.
Text copied from web pages and PDFs: this is where invisible web spaces and zero-width characters come from.
Before a VLOOKUP, an XLOOKUP or a pivot table: clean both lists first, so matching values really do match.
Before comparing two versions of a sheet: stray spaces and capitals otherwise show up as false differences.
Mailing lists and customer lists: consistent names and lower-case email addresses, in one run.
Files that came back from several people: everyone types differently, and this brings it all to one style.

Demo: Clean Data in Excel, Start to Finish

This tutorial shows how to clean data in Excel from start to finish, using the example on this page: finding the problems, choosing the options, checking the result and cleaning only the filtered rows.

Frequently Asked Questions

How do I clean data in Excel without formulas?

Select your cells, go to the DOSE tab and click Data Cleaning. Tick what you want fixed: spaces and hidden characters, capitals, or numbers stored as text. Then click Apply. The cells are cleaned where they are, with no helper columns to fill and delete.

Can I trim spaces, change case and convert text to numbers in one go?

Yes. Tick any combination of the three groups and they all run on the same selection when you click Apply.

Does it remove the non-breaking spaces that TRIM misses?

Yes. These invisible web spaces (character 160) are treated as ordinary spaces when you use Trim spaces at the beginning/end of text or Remove all excess spaces. They are a common reason for VLOOKUP or XLOOKUP failing on data copied from a web page.

What happens to line breaks inside a cell?

With Remove line breaks in cells ticked, each break becomes one space, so the words on either side stay apart. Untick it and the line breaks are kept, even if you remove other non-printing characters.

Will it change my formulas or order numbers like 00123?

No. Formulas stay formulas, and error values are left as they are. Text to numbers leaves values with leading zeros as text, so 00123 keeps its zeros.

Will changing case affect my numbers or dates?

No. The case options only change text. Numbers and dates are skipped, including text that looks like a number or a date, so you can run them across a mixed column.

Can I keep a copy of the original sheet?

Yes. Tick Backup sheet and click Yes when Dose asks. It adds a copy of the whole sheet next to the original before anything is cleaned, and the copy keeps your data exactly as it was.

Does Data Cleaning work on filtered data?

Yes. Only the visible cells are changed. Rows hidden by a filter, and any hidden rows or columns, stay as they were.

Do I need to select the whole sheet?

No. It works on what you select: a range, a whole column, several separate ranges or the whole sheet. Selecting a whole column or the whole sheet only goes through the part of the sheet that holds data.

Which Excel versions does Data Cleaning work with?

Excel 2010, 2013, 2016, 2019, 2021, 2024 and Microsoft 365, in 32-bit and 64-bit editions, on Windows. The interface is available in multiple languages.

Related Dose for Excel Tools

These tools work well with Data Cleaning, when you need one specific job or the next step: