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

Messy Import Data Example

Notice the problems: dates in different formats, numbers with symbols and mixed separators, names with extra spaces and inconsistent casing.

	A	B	C	D
1	Date	Amount	Name	Status
2	15/03/2024	$ 1.234,56	john DOE	Active
3	20-03-2024	2,500.00	maria SMITH	Active
4	25/03/24	$3,000	PETER	Pending
5	30/03/2024	1.500,00	ANNA JONES	Active
6	05/04/2024	$ 2.000	DAVID BROWN	Pending

fxCells with formulas are highlighted in green

Hover over formula cells to see the formula and highlight referenced cells

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

Text pretending to be number: =SUMPRODUCT(--ISTEXT(A2:A100))
Numbers truly numeric: =SUMPRODUCT(--ISNUMBER(A2:A100))
Blanks: =COUNTBLANK(A2:A100)
Duplicates: =SUM(--(COUNTIF(A2:A100, A2:A100)>1))
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)

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

7) Mini Exercises (Fast Practice)

Dates: Given 15/03/25 as text, convert reliably to a real date and format as dd mmm yyyy.
Numbers: Clean "$ 1.234,56" into a numeric value.
Names: Convert " maria LOPEZ " into "Maria Lopez".
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.