lesson
30 min

Messy Data and How to Clean It

Spot the common ways real data is messy and clean it before it reaches the next step.

Objective

By the end of this lesson, you can find and clean four common kinds of messy data: phone numbers, names, dates, and numbers typed as words.

Automations copy mistakes fast

A person who sees "about 40" in a guest count knows what it means. An automation does not. It copies it, or it breaks.

Data hygiene means keeping data clean, consistent, and in the right format. Think of it like washing your hands in a kitchen. It is not exciting, but skipping it makes everything after it worse.

Four common messes

MessWhat people typeClean version
Phone numbers(210) 555-0147, 210.555.0147, 2105550147210-555-0147
NamesmARIA lopez, " Maria Lopez "Maria Lopez
Dates3/4, March 4th, 04-03-2026One format, always with the year
Numbers as wordsabout 40, 40ish, forty40, with a flag that it was a guess

Dates are tricky. "3/4" means March 4 in the U.S. and April 3 in many other countries. Use a date picker on your form so people cannot type it freely.

Prevent first, clean second

The best fix is at the start, in the form:

  • Use a date picker instead of a text box for dates.
  • Use a number field for guest counts.
  • Use dropdowns for fixed choices, like shift or service type.
  • Mark fields required only if you truly need them.

Then clean what still comes through. Most tools have built-in formatter steps. Zapier calls it Formatter. Make has built-in text and date functions. n8n has a Set node and code steps.

Try a cleaner

This code cleans a phone number and a guest count. Run it, then try your own messy examples.

Clean a sign-up

Run it. Then change the phone to '210.555.0147' and the guests to 'forty'. Which one does the cleaner handle? Keyboard users: press Esc, then Tab, to leave the editor.

Loading the code editor...

Press Esc, then Tab, to move focus out of the editor.

Notice that the cleaner does not guess when it is stuck. It marks the field CHECK so a person looks at it. Words like "forty" stay flagged. That is a judgment call built into code: when in doubt, ask a person.

Should AI clean data?

AI can turn "forty-ish" into 40. But it can also turn a phone number into a different phone number with a typo. Use plain rules for data that has one right format, like phones and dates. Save AI for messy text that rules cannot handle, and flag its output for review.

Try this: Look at the last 20 rows of your partner's spreadsheet or form responses. Count every messy field you find. Which mess is the most common?