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
| Mess | What people type | Clean version |
|---|---|---|
| Phone numbers | (210) 555-0147, 210.555.0147, 2105550147 | 210-555-0147 |
| Names | mARIA lopez, " Maria Lopez " | Maria Lopez |
| Dates | 3/4, March 4th, 04-03-2026 | One format, always with the year |
| Numbers as words | about 40, 40ish, forty | 40, 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.
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?