Cleaning Messy Data Imports: Standardizing Dates, Numbers, and Text

Messy CSVs, ERP dumps, or report exports are universal. This guide is a fast, formula-first cleanup routine you can reuse on every import — no Power Query or VBA required.

1) Spot the Dirt Fast

• Numbers as text? =ISTEXT(A2) vs =ISNUMBER(A2)
• Hidden blanks? =COUNTBLANK(A:A) and =LEN(A2) to catch space-only cells
• Duplicate checks: =COUNTIF(A:A, A2) > 1 means repeated entries
• Out-of-pattern dates: =ERROR.TYPE(--A2) to see if coercion fails

Mini check panel (5 cells you can copy anywhere):

1. Text pretending to be number: =SUMPRODUCT(--ISTEXT(A2:A100))
2. Numbers truly numeric: =SUMPRODUCT(--ISNUMBER(A2:A100))
3. Blanks: =COUNTBLANK(A2:A100)
4. Duplicates: =SUM(--(COUNTIF(A2:A100, A2:A100)>1))
5. Max length (find odd strings): =MAX(LEN(A2:A100))

2) Fix Dates Safely (Region-Aware)

When dates arrive as text:

• Basic coercion: =--A2 (fails if day/month order mismatches)
• Explicit parse with TEXTSPLIT (for dd/mm/yyyy style):

  =LET(
    parts, TEXTSPLIT(A2, "/"),
    day, INDEX(parts,1),
    month, INDEX(parts,2),
    year, INDEX(parts,3),
    DATE(year, month, day)
  )
  

• Handle hyphens or mixed separators: swap "/" for {"-","/"} in TEXTSPLIT.
• Guardrail: =IFERROR(parsedDate, "Check date")

Detect and standardize mixed formats by creating a helper column with the parsed date; once clean, convert to values (Copy → Paste Special → Values).

3) Clean Numbers with Symbols and Spaces

Common issues: currency symbols, non-breaking spaces, thousands separators, comma/period swaps.

• Strip symbols/spaces, then convert:

  =VALUE(SUBSTITUTE(SUBSTITUTE(TRIM(A2), "$",""), " ",""))
  

  (the second substitute removes non-breaking spaces)

• Fix comma vs period decimals (e.g., "1.234,56"):

  =VALUE(SUBSTITUTE(SUBSTITUTE(A2,".",""),",","."))
  

• Remove thousands separators only:

  =VALUE(SUBSTITUTE(A2, ",", ""))
  

Use IFERROR(...,"Check number") to flag rows that still fail.

4) Normalize Text Fields

Names / multi-part text

• Split: =TEXTSPLIT(A2, " ")
• Proper case: =PROPER(A2)
• Rejoin without extra spaces:

  =TEXTJOIN(" ", TRUE, TEXTSPLIT(TRIM(A2)," "))
  

Emails / IDs

• Lowercase: =LOWER(A2)
• Remove leading/trailing spaces: =TRIM(A2)

Consistent labels

• Title-case labels but keep abbreviations? Use helper columns and SUBSTITUTE after PROPER (e.g., replace "Uk" back to "UK").

5) De-duplicate and Validate

• Flag duplicates: =COUNTIF(A:A, A2)>1
• Unique list for review: =UNIQUE(A2:A)
• Validate key columns (no blanks, all unique):

  =IF(OR(A2="", COUNTIF(A:A, A2)>1), "Fix", "OK")
  

Use conditional formatting with =ISERROR(A2) or =COUNTIF($A:$A, A1)>1 to highlight issues.

6) A Reusable “Import Hygiene” Checklist (Copy/Paste)

1. Dates parsed? Try --A2; if errors, use TEXTSPLIT + DATE.
2. Numbers numeric? =ISNUMBER(A2); if not, strip symbols and VALUE.
3. Spaces cleaned? =LEN(A2) vs =LEN(TRIM(A2)).
4. Case normalized? Apply LOWER/PROPER where needed.
5. Duplicates handled? =COUNTIF(A:A,A2) and UNIQUE() for a clean list.

7) Mini Exercises (Fast Practice)

1. Dates: Given 15/03/25 as text, convert reliably to a real date and format as dd mmm yyyy.
2. Numbers: Clean "$ 1.234,56" into a numeric value.
3. Names: Convert " maria LOPEZ " into "Maria Lopez".
4. Duplicates: Highlight any repeated IDs in B2:B50 and produce a unique list.

Wrap-Up

Messy imports are unavoidable; slow cleanup is optional. Keep this checklist, drop the formulas into a small staging area, and convert to values once clean. You’ll standardize dates, numbers, and text in minutes — and avoid downstream formula errors.