Cleaning Roster Datafor Union Campaign Success
Module 2 · Spaces, Line Breaks, and Invisible Characters › 2.2 TRIM, CLEAN, and SUBSTITUTE
Subsection 2.2

TRIM, CLEAN, and SUBSTITUTE

~6 min

Reading

Once you know what each function does on its own, the next move is reading a nested formula inside-out. The formula you will write at the checkpoint is:

=SUBSTITUTE(CLEAN(TRIM(B17)), CHAR(160), "")

It looks intimidating until you peel it apart.

=SUBSTITUTE(CLEAN(TRIM(B17)), CHAR(160), "") SUBSTITUTE Replace one piece of text with another. Outer wrapper. Runs last. CLEAN Strips non-printing characters, including CHAR(10) line breaks. Runs second. TRIM(B17) Removes leading, trailing, and double spaces from B17. Runs first. CHAR(160) The non-breaking space. Invisible to the eye, lethal to matching. "" Empty string. Replace the non- breaking space with nothing.
Diagram 2.2 · Anatomy of a nested whitespace formula. Read inside-out: TRIM runs first, then CLEAN, then SUBSTITUTE swaps any remaining non-breaking space for nothing.

Action: Mark this page complete when you have finished the activity above.