The Usual Problems

Six problems, in roughly the order you meet them.

Whitespace, including non-breaking spaces that look identical to ordinary ones and compare unequal.

Inconsistent case, so NY, ny and New York become three categories.

Several spellings of one thing, which is the hardest to fix correctly and the easiest to fix wrongly.

Dates in mixed formats, where 03/04 is ambiguous without knowing the source's country.

Duplicates, exact and near.

Missing values, written half a dozen ways in one column.

Never clean in place. Read raw, write clean, keep both. Cleaning should be a script in version control, because a script can be re-run, reviewed and corrected, and a spreadsheet edited by hand cannot be any of those things.

The day you find a bug in your cleaning is the day this decision pays for itself.