Spreadsheet Practice

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)
Pro8 minutes, 3 exercisesTRIMPROPERLOWERSUBSTITUTE

Watch it done

3 steps

Watch 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 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

  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.

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.

  1. Clean names
  2. Clean emails
  3. Digits only