To clean imported data in Excel, five functions handle almost every problem: TRIM removes extra spaces, PROPER, UPPER and LOWER standardize case, VALUE converts text-numbers to real numbers, DATEVALUE converts text dates, and CLEAN with SUBSTITUTE(A2, CHAR(160), " ") removes the non-breaking spaces that CSV exports leave behind. They can be combined into a single formula and then pasted back as values.
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.
Common problems in imported CSV data
Here’s what messy imported data typically looks like:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Amount | Date | |
| 2 | JOHN SMITH | John.Smith@EXAMPLE.COM | 1500 | 03-15-2026 |
| 3 | jane doe | jane@example.com | $2,300.00 | 2026/03/16 |
| 4 | Robert Johnson | RJOHNSON@EXAMPLE.COM | 800 | March 17, 2026 |
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@EXAMPLE.COM” → “john.smith@example.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-2026” or “March 17, 2026” 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. “2026/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.
Common questions
Does cleaning change my original data?
No. These formulas write results to new columns and leave the source untouched. To replace the originals, copy the cleaned column and use Paste Special, Values.
Why does TRIM leave spaces behind?
Because they are non-breaking spaces, CHAR(160), which web and CRM exports use and which TRIM does not remove. Substitute them first: =TRIM(SUBSTITUTE(A2, CHAR(160), " ")).
Can I clean data in place without helper columns?
Not with formulas, since a formula cannot sit in the cell it reads. Use helper columns and then paste values over the originals, or use Power Query, which transforms in place.
Doing this every week? There is a tool for it
The formulas on this page work, but they are a manual pass every time the file arrives. If the file is a contact list, Excel Data Cleaner can take over much of that pass: it lowercases emails and removes their stray spaces, fixes the casing of names, puts phone numbers and dates into one format, then finds and removes duplicates. It shows you every proposed change before anything is written.
Free up to 100 records per file, and it runs entirely on your own PC.
Headed for a CRM? Add-on exporters turn the cleaned list into an import-ready file for HubSpot, Salesforce, Zoho, Pipedrive or monday.
See Excel Data CleanerWindows PC required: it runs inside Excel on Windows, not on a phone, a tablet or a Mac. Why it does not run on Mac.
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.
Small fixes start at $69.
Every project gets a fixed-price quote before any work starts: the price you approve is the price you pay.