You import a CSV from your accounting system, a vendor export, or a CRM dump — and the data is a mess. Numbers stored as text. Extra spaces everywhere. Inconsistent capitalization. Dates in four different formats. Before you can do anything useful with this data, it needs to be cleaned. Here are the essential formulas and techniques for fixing the most common problems.
The Usual Suspects
Here’s what messy imported data typically looks like:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Amount | Date | |
| 2 | JOHN SMITH | John.Smith@EMAIL.COM | 1500 | 03-15-2025 |
| 3 | jane doe | jane@company.com | $2,300.00 | 2025/03/16 |
| 4 | Robert Johnson | RJOHNSON@CORP.NET | 800 | March 17, 2025 |
Every column has a different problem: extra spaces, inconsistent casing, numbers stored as text, mixed date formats. Let’s fix each one.
Fix 1: Remove Extra Spaces with TRIM
=TRIM(A2)
Removes all leading spaces, trailing spaces, and reduces multiple spaces between words to a single space. ” JOHN SMITH ” becomes “JOHN SMITH”.
Hidden characters
Some imports include non-breaking spaces (character 160) that TRIM doesn’t catch. Use =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))) to handle everything — SUBSTITUTE replaces non-breaking spaces, CLEAN removes non-printable characters, and TRIM handles the rest.
Fix 2: Standardize Text Case with PROPER, UPPER, LOWER
Proper Case (for names)
=PROPER(TRIM(A2))
“JOHN SMITH” → “John Smith” | “jane doe” → “Jane Doe”
Lowercase (for emails)
=LOWER(TRIM(B2))
“John.Smith@EMAIL.COM” → “john.smith@email.com”
Fix 3: Convert Text-Numbers to Real Numbers
When amounts import as text (left-aligned, won’t SUM), you need to force them into numbers:
=VALUE(SUBSTITUTE(SUBSTITUTE(TRIM(C2), "$", ""), ",", ""))
Strips dollar signs and commas, trims spaces, then converts to a number. “$2,300.00” → 2300. “1500 ” → 1500.
Quick test
How to tell if a number is stored as text: select the cell and look at the alignment. Real numbers right-align by default; text left-aligns. You might also see a small green triangle in the top-left corner of the cell — that’s Excel’s warning that a number is stored as text.
Fix 4: Remove Extra Spaces Between Words
“Robert Johnson” has multiple spaces between first and last name. TRIM handles this too — it reduces any run of multiple spaces to a single space:
=TRIM(A4)
“Robert Johnson” → “Robert Johnson”
Fix 5: Standardize Mixed Date Formats
When dates come in as text in various formats, DATEVALUE can convert recognizable patterns:
=DATEVALUE(D2)
Converts text dates like “03-15-2025” or “March 17, 2025” into real Excel date values. Format the result cell as a date afterward.
Doesn’t always work
DATEVALUE depends on your system’s date settings and the text format. “2025/03/16” may not convert depending on your locale. For truly inconsistent date formats, manual parsing with MID, LEFT, and RIGHT or a VBA script is more reliable.
The All-in-One Cleanup Formula
For a name column, combine everything into one formula:
=PROPER(TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))))
Handles non-breaking spaces, non-printable characters, extra spaces, and inconsistent casing in a single formula.
Before and After
| A (Original) | B (Cleaned) | |
|---|---|---|
| 1 | Name | Cleaned Name |
| 2 | JOHN SMITH | John Smith |
| 3 | jane doe | Jane Doe |
| 4 | Robert Johnson | Robert Johnson |
Paste Values to Replace Originals
Once your cleanup formulas are working, you’ll want to replace the messy originals with the clean values:
1Select the column with your cleanup formulas
2Copy (Ctrl+C)
3Right-click the original column → Paste Special → Values
4Delete the helper column with the formulas
Why Paste Values?
If you delete the original column while your cleanup formulas still reference it, they’ll break (#REF! errors). Pasting as values converts the formulas to static text, so the originals can be safely removed.
When Formulas Aren’t Enough
Cleaning a one-time import with formulas is manageable. But if you’re dealing with recurring messy data, it quickly becomes unsustainable:
- Weekly or monthly imports — rebuilding cleanup formulas every time is repetitive and error-prone
- Dozens of columns — each column might need different cleanup rules
- Business logic rules — standardizing state abbreviations, phone formats, or company name variations requires lookup tables and complex logic
- Multi-file processing — cleaning and combining imports from 5+ sources at once
- Validation and flagging — identifying records that can’t be auto-cleaned and flagging them for manual review
A custom VBA data cleaning tool applies all your cleanup rules in one click — TRIM, standardization, format conversion, validation, and error flagging — across any number of columns and files.
“Sublime. Honestly can’t rate highly enough.”
Jan H. · PPH
Cleaning the Same Messy Import Every Week?
Tell us about your data and we’ll build a one-click cleanup tool.
Start a Project