Spreadsheet Practice

Lesson 15 of 29, Text cleanup

Cleaning messy data

Fix spacing, capitals and stray characters in imported data.

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.

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 27 Pro lessons, Excel and Google Sheets, 79 exercises in all.

  1. Clean names
  2. Clean emails
  3. Digits only
Try these functions in the Formula Lab