Module 2 · You are here

Spaces, Line Breaks, and Invisible Characters

Master TRIM, CLEAN, and SUBSTITUTE so the silent destroyers of matching — leading spaces, hidden line breaks, non-breaking spaces — stop hiding workers from the housecall route.

Time~30 minutes

BadgeWhitespace Wrangler

DeviceDesktop recommended

ArtifactCleaned phone, name, and email columns

By the end of Module 2, you will be able to:

Explain where leading and trailing spaces come from in the real world.
Use =TRIM() to remove leading, trailing, and double spaces.
Use =CLEAN() to strip non-printing characters and line breaks.
Use =SUBSTITUTE(A1, CHAR(160), "") to remove non-breaking spaces.
Use Find and Replace with regex to fix patterns across a whole column.

Subsections in Module 2

2.1

Where dirty whitespace comes from

4 min

Open 2.2

TRIM, CLEAN, and SUBSTITUTE

6 min

Open 2.3

Hands-on: clean the phone column

8 min

Open 2.4

Find and Replace with regex

5 min

Open 2.5

Whitespace checkpoint

5 min

Open

Start here: Module 2.1 · Where dirty whitespace comes from

Begin Module 2

After Module 2: Module 3 · Splitting Concatenated Fields

Next: Module 3 · Splitting Concatenated Fields