Skip to content
Tutorials Center
My learning

Topics

Lesson 68 of 93 · Data Cleaning and Import

Cleaning Messy Data Step by Step

Clean messy Excel data step by step: trim spaces, fix case, split columns, convert numbers stored as text, handle blanks and errors, plus a repeatable checklist.

Real data is messy. Names have trailing spaces, "USA" is also written "U.S.A." and "United States", numbers are stored as text, blank rows sit in the middle of a list, and a few cells contain #N/A. None of that matters until you try to sort, filter, look up or pivot, and then everything quietly goes wrong: a VLOOKUP misses a match because of one invisible space, and a PivotTable shows "North" twice.

This lesson walks through a practical clean-up in a sensible order and ends with a checklist you can reuse. The Excel steps are text, because Excel cannot run on this site. The live examples are stand-ins: JavaScript functions that mimic what TRIM, PROPER, SUBSTITUTE and friends do, and pandas code that does the same jobs on a whole table.

A clean-up in six moves

Trim Remove stray spaces.
Standardize One spelling, one case.
Split One fact per column.
Convert Text to real numbers.
1 / 2

Rule zero: keep the original

Before changing anything, keep an untouched copy of the raw data: a hidden sheet, a separate file, or just the original export. Clean in new columns with formulas first, check the result, and only then paste the cleaned values over or beside the original with Paste Special > Values. If you also use a query (see Power Query: Get and Transform), the raw data stays untouched automatically and every step is recorded.

Step 1: Trim spaces and invisible characters

TRIM(text) removes leading and trailing spaces and reduces runs of spaces inside the text to one. Spaces copied from web pages can be non-breaking spaces (character code 160), which TRIM does not touch. CLEAN removes the first 32 non-printing characters, such as line breaks and tabs. A reliable combination is:

TEXT
=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

Check the damage with =LEN(A2) compared to =LEN(TRIM(A2)): if they differ, hidden spaces exist. More text functions are in Text Functions: LEFT, RIGHT, MID, LEN, TRIM.

Step 2: Standardize case and spelling

UPPER, LOWER and PROPER change case. PROPER("o'brien") gives O'Brien, but PROPER("mcdonald") gives Mcdonald and it capitalizes after every non-letter, so review names afterwards. For spelling variants (USA, U.S.A., United States), use a small mapping table with a lookup, or Find and Replace for a handful of cases:

  • Ctrl+H opens Find and Replace. Replace All works on the selection if you selected cells first, otherwise on the whole sheet.
  • In Find what, ? matches any single character and * matches any run of characters. Mr* finds "Mr", "Mrs" and "Mr. Smith". To find a real asterisk or question mark, put a tilde in front: ~*.
  • Match entire cell contents prevents replacing parts of longer text ("US" inside "BUS").

Wildcards also work in COUNTIF, SUMIF and XLOOKUP (see SUMIF, COUNTIF, AVERAGEIF and Their IFS Versions).

JS Clean text like TRIM, SUBSTITUTE and PROPER

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Step 3: Split combined values

If one cell holds several facts ("Souza, Ana" or "Lisbon - Portugal"), split it into columns so each fact can be sorted and filtered. Options:

  • Data > Text to Columns: delimited or fixed width; it overwrites the cells to its right, so make space first.
  • Flash Fill (Ctrl+E): type the expected result in the next column once or twice and Excel infers the pattern. It is quick but static and sometimes guesses wrong, so verify.
  • TEXTSPLIT, TEXTBEFORE, TEXTAFTER in current Excel, or LEFT/MID/FIND in older versions; see Joining and Splitting Text.

Step 4: Convert numbers stored as text

Signs of numbers stored as text: left-aligned values, a small green triangle in the corner, a Sum that is lower than expected, and a status bar that shows only Count. Typical causes are imported data, a leading apostrophe, trailing spaces, or thousands separators in the wrong locale. Fixes:

  • Select the cells, click the warning icon and choose Convert to Number.
  • Data > Text to Columns > Finish without changing anything converts the selected column in place.
  • Multiply by one with Paste Special: type 1 in a cell, copy it, select the text numbers and use Paste Special > Multiply.
  • With formulas: =VALUE(TRIM(A2)), or =--TRIM(A2). For other locales use NUMBERVALUE; see TEXT, VALUE, NUMBERVALUE and Number-as-Text Problems.

Non-breaking spaces and symbols such as $ or kg must be removed first with SUBSTITUTE.

Step 5: Deal with blanks

First decide what a blank means: unknown, zero, or "same as above". Then:

  • Delete blank rows: select the column, press F5 > Special > Blanks, then right-click > Delete > Entire row. Be careful: this deletes a row if that column is blank even when the other columns have data. Select a column that is always filled in the real records.
  • Fill down missing group labels: select the range, F5 > Special > Blanks, type = and press the Up arrow, then Ctrl+Enter. Convert the formulas to values afterward.
  • Replace blanks with 0 or "Unknown" only if that is truthful. Zero and "no data" are different things in an average.
  • Remember that a cell with "" returned by a formula looks blank but is not: COUNTBLANK counts it, ISBLANK does not.

Step 6: Find and handle errors

Use the filter dropdown (error values such as #N/A are listed as items you can tick) or use Home > Find & Select > Go To Special > Formulas > Errors. Look for the cause: #N/A often means a failed lookup caused by a trailing space or mismatched types; #DIV/0! means an empty denominator. Fix the data, not just the symptom. Wrap with IFERROR only when you have decided what a good fallback is, because it can hide real mistakes (see Handling Errors and Blanks in Formulas).

Step 7: Duplicates and consistency

Look for exact duplicates only after trimming and standardizing, or you will miss "Ana" and "Ana ". Details are in Finding and Removing Duplicates. Also check consistency with Data Validation lists and a quick PivotTable of each text column, which lists every distinct spelling with its count.

The same clean-up in pandas

pandas has a method for each step. This stand-in cleans a small table in the order we just used.

Python A repeatable clean-up with pandas

Press Run (or Ctrl + Enter) to see the result here.

Program input · what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice errors="coerce": the text n/a became a missing value, like #VALUE! in Excel. Then we chose to replace it with zero. Make that choice on purpose and write it down.

A repeatable checklist

  1. Copy the raw data. Never overwrite the only copy.
  2. Make sure there is one header row, one record per row, no merged cells, no subtotal rows.
  3. Trim and clean text; replace non-breaking spaces.
  4. Standardize case and spelling (mapping table).
  5. Split combined columns.
  6. Convert numbers and dates stored as text.
  7. Handle blanks deliberately.
  8. Resolve errors at their source.
  9. Remove duplicates last, after the data is consistent.
  10. Check: row counts, totals, a PivotTable of each text field, and a spot-check of ten random rows.

If you will repeat this monthly, build it in Power Query once and press Refresh.

Check your understanding

Quick check · 4 questions

of 4 answered You scored / 4 · perfect!

  1. 1. A VLOOKUP fails for "Ana" although "Ana" is in the list. Which cause is most likely?

    Ana and Ana-with-a-space are different text. TRIM removes the stray space; compare LEN before and after to find it.
  2. 2. What does TRIM NOT remove by default?

    TRIM only handles the ordinary space (code 32). Replace CHAR(160) with a normal space using SUBSTITUTE first.
  3. 3. In Find and Replace, how do you search for a literal asterisk?

    The asterisk is a wildcard, so a tilde in front makes it literal. The same works for the question mark and the tilde itself.
  4. 4. Why should you remove duplicates after trimming and standardizing the text?

    Duplicate detection compares exact values, so inconsistent spacing or case hides duplicates.

Summary

  • Keep the raw data, clean in new columns or a query, and check before you overwrite.
  • TRIM, CLEAN and SUBSTITUTE remove spaces and hidden characters; PROPER, UPPER and LOWER fix case; a mapping table fixes spelling.
  • Split with Text to Columns, Flash Fill or the TEXT functions; convert numbers stored as text with Convert to Number, VALUE or Paste Special.
  • Treat blanks and errors deliberately: find the cause before you hide it.
  • Remove duplicates last and finish with row-count, total and spot checks.

Next: Power Query: Get and Transform.