Data cleaning turns a messy export into data you can trust: no stray spaces, one case per column, codes that match. In Excel it takes a handful of text functions.
B2 = Marco·Rossi TRIM took off the two spaces in front, and PROPER capitalized each word.
Exports never arrive clean. Names come in with stray spaces and in capitals, codes have spaces inside them, and the lookups and counts you run on them quietly come out wrong: a code that won’t match, one customer counted twice. CRM exports, bank statements and survey downloads all arrive like this, so a lot of real spreadsheet time goes into cleaning them.
It turns up in employer Excel tests for the same reason, and it’s easy to check: either the column comes out clean or it doesn’t. Below you clean a customer export with formulas, step by step, then see each function taken apart on a small sheet where you can make the classic mistakes yourself. The full 128-row version is in the downloadable practice dataset.
This is Witsheet, Witsy’s in-browser spreadsheet. It works like Excel and checks each step as you go.
TRIM takes one argument, the text, and gives it back with the spaces fixed: none at either end, and never more than one in a row. Spaces are the commonest fault in an export because they can’t be seen. On the sheets here each one is drawn as a faint dot.
B2 = marco·rossi The two spaces in front are gone. TRIM fixes spacing only: the case is still wrong, and that’s PROPER’s job.
Three functions set the case, each taking the text as its one argument. PROPER gives each word a capital, UPPER makes everything capitals and LOWER makes everything lowercase. Pick by what the column holds: PROPER for names, UPPER for codes, LOWER for email addresses.
B2 = Johan·Berg One capital at the start of each word: the case for names.
Most columns have more than one fault, and each function fixes one. So you write them inside each
other: =PROPER(TRIM(A2)) trims the name, then capitalizes what TRIM gives back. A formula
works from the inside out, and the order you type it in is the other way round.
B2 = ··Marco··Rossi· PROPER fixes the case and nothing else: the two spaces in front, the double space and the one at the end all come through.
Leave the raw column as it is and build the clean one beside it, as the walkthrough does. The clean column still points at the raw one, though: delete the raw data and the formulas break. To finish, copy the clean column, paste it back with Paste Special → Values, then delete the raw one.
TRIM leaves a single space between words alone, because between words it belongs. In a code like
“CU 1041” it doesn’t, and SUBSTITUTE takes it out. It finds every copy of one piece of text and
swaps it for another, and swapping for "" means removing.
B2 = CU1041 The space is swapped for nothing, so it’s gone.
SUBSTITUTE matches exact text, capitals included. When a column mixes spaces and hyphens, put one
SUBSTITUTE inside another: =SUBSTITUTE(SUBSTITUTE(A2, " ", ""), "-", "").
PROPER has one rule: a capital after anything that isn’t a letter, lowercase everywhere else. Most names follow it, including hyphens and apostrophes. Some don’t, and PROPER is confidently wrong about them.
B2 = Al-Sayed The hyphen isn’t a letter, so both halves get their capital. Right.
So scan a PROPER column before you trust it, and fix the few it gets wrong by hand. Flash Fill (Ctrl+E) can do the same cleaning from two examples you type, but it fills values, not formulas, so it won’t update when the raw column does.
An import sometimes brings numbers in as text: characters that look like 49.90 but aren’t a number to Excel. They sit on the left of the cell, where numbers sit on the right, and SUM leaves them out of the total without a word.
B7 = 290 The spends come to 339.90, but SUM skips 49.90: it’s text that looks like a number, and SUM passes over text without a warning.
To find them, compare =COUNT() with =COUNTA() on the column, or flag each
row with =ISTEXT(B2). To fix them, Excel marks each one with a small green triangle:
select the cells, click the warning and choose Convert to Number. Or build a clean column with =VALUE(B2), as the messy dataset’s tasks ask. A number stored as text also makes a
lookup miss, which is the other common cause of a #N/A on a value you can see; VLOOKUP practice covers the lookup side.
To carry on in your own Excel, download the messy dataset. It’s the same job at full size: 128 customer rows the way real exports arrive, with stray spaces, mixed casing, dates in three formats, numbers stored as text and duplicate rows, and a tasks sheet to work through.
A customer export the way real exports arrive: inconsistent casing, stray spaces, duplicate rows, and numbers stored as text. Practice TRIM, PROPER, Flash Fill and de-duplication.
128 rows · Customer Name · Email · City · Country · Signup Date · Monthly Spend
Free · fictional data · opens in Excel and Google Sheets
Witsy’s Excel course teaches a few hundred small skills, called Wits, the way this page teaches data cleaning: in a real spreadsheet, a step at a time. The placement test finds the ones you already have.
Take a few minute test to see how good you actually are with spreadsheets. Witsy creates a map of the Wits you should learn next.
Take the placement test Or just start learning →