Lesson 20 of 45, Text cleanup
Cleaning messy data
Quick answer
Fix spacing, capitals and stray characters in imported data.
- Key formula
=PROPER(TRIM(A2))(Name Case, single spaces)
Watch it done
3 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
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
=PROPER(TRIM(A2))Name Case, single spaces=LOWER(TRIM(C2))Clean email=SUBSTITUTE(E2,"-","")Remove dashesWatch 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
- Clean names. In B2, remove extra spaces and convert to Proper Case. Fill down to B6.
- Clean emails. In D2, remove the stray spaces and make each email lowercase. Fill down to D6.
- Digits only. In F2, remove the dashes from each phone number. Fill down to F6.
Questions people ask about this
Updated Checked by running: every exercise answer on this page was graded by our formula engine on .
Graded practice for this lesson is part of Pro
The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 43 Pro lessons, Excel and Google Sheets, 125 exercises in all.
- Clean names
- Clean emails
- Digits only