# Cleaning messy data (Excel lesson 20 of 45)

> Fix spacing, capitals and stray characters in imported data.

Source: https://spreadsheetpractice.com/learn/cleaning-messy-data · Updated October 29, 2026

- **Course:** [Excel course](https://spreadsheetpractice.com/learn), track "Text cleanup"
- **Time:** about 8 minutes, 3 graded exercises
- **Functions:** `TRIM`, `PROPER`, `LOWER`, `SUBSTITUTE`
- **Access:** Pro (the lesson text is free; the graded exercises need Pro)
- **Key formula:** `=PROPER(TRIM(A2))`: Name Case, single spaces

## How it works

Exports from other systems arrive with double spaces, random capitals and punctuation you don't want. `TRIM` removes extra spaces; `PROPER`, `UPPER` and `LOWER` fix capitalization; `SUBSTITUTE` swaps one piece of text for another.

Nest them. The innermost function runs first: `=PROPER(TRIM(A2))` trims, then capitalizes.

## Syntax

| Formula | What it does |
|---|---|
| `=PROPER(TRIM(A2))` | Name Case, single spaces |
| `=LOWER(TRIM(C2))` | Clean email |
| `=SUBSTITUTE(E2,"-","")` | Remove dashes |

## Watch out for

- TRIM keeps one space between words; it only removes extras.
- Lookups fail silently when one side has a trailing space. Clean both sides.

## The exercises

Solve them in the live grid at [Cleaning messy data](https://spreadsheetpractice.com/learn/cleaning-messy-data). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **Clean names.** In B2, remove extra spaces and convert to Proper Case. Fill down to B6.
2. **Clean emails.** In D2, remove the stray spaces and make each email lowercase. Fill down to D6.
3. **Digits only.** In F2, remove the dashes from each phone number. Fill down to F6.

Previous: [Joining text](https://spreadsheetpractice.com/learn/joining-text) · Next: [TEXTBEFORE, TEXTAFTER & FIND](https://spreadsheetpractice.com/learn/textbefore-textafter-find)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
