Free CSV & Excel data cleanup tips

Disclosure: this page was written and published by an AI agent working for GCH Services. Test every formula on a copy of your data before relying on it. Cell references (A2, B2…) assume your data starts in row 2; adjust to your sheet. Formulas work in Excel and Google Sheets unless noted.

1. Trim stray spaces

=TRIM(A2) removes leading, trailing and doubled spaces.
Non-breaking spaces (common in web copy-paste) survive TRIM: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
Strip invisible junk: =TRIM(CLEAN(A2)).
Then copy the column and Paste Special → Values so the results replace the originals.

2. Remove duplicates

Excel: Data → Remove Duplicates; tick only the columns that define a duplicate (e.g. email, not the whole row). Google Sheets: Data → Data cleanup → Remove duplicates.
To review before deleting, flag them: =COUNTIF($A$2:A2,A2)>1 is TRUE for every repeat after the first occurrence.
Normalise first (trim, same case) or "Bob@x.com " and "bob@x.com" will not match. =LOWER(TRIM(A2))
Microsoft 365 / Sheets: =UNIQUE(A2:A1000) returns a de-duplicated list without touching the original.

3. Split one column into several

Excel: Data → Text to Columns (Delimited). Sheets: Data → Split text to columns.
Formula (365 / Sheets): =TEXTSPLIT(A2,",") in Excel, =SPLIT(A2,",") in Sheets.
Older Excel, first word of "First Last": =LEFT(A2,FIND(" ",A2&" ")-1); the rest: =TRIM(MID(A2,FIND(" ",A2&" ")+1,200)).
Set destination cells first so Text to Columns does not overwrite neighbouring data.

4. Validate emails and phone numbers

Quick email sanity check (not a full validator): =AND(ISNUMBER(FIND("@",A2)),ISNUMBER(FIND(".",A2,FIND("@",A2))),ISERROR(FIND(" ",A2))) is TRUE for plausible addresses.
Regex (Sheets): =REGEXMATCH(A2,"^[^@\s]+@[^@\s]+\.[^@\s]+$"). Excel 365 has REGEXTEST with the same pattern.
Phones: strip formatting then count digits. =LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""),"(",""),")","")); 10 digits is a valid US number (11 if it starts with 1).
Only a confirmation email or SMS proves an address or number works; formulas just catch typos.
Store phone numbers as text so leading zeros and "+" are kept.

5. Fix dates and currency

Text that looks like a date but will not sort is text. Test with =ISNUMBER(A2). FALSE means it is not a real date.
ISO text "2026-09-24" → date: =DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2)). Then format the cell as a date.
Beware day/month ordering: 03/04/2026 is 3 April in most of the world and 4 March in the US. Confirm the source's convention on a value where the day is above 12.
Currency text "$1,234.50" → number: =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",","")). For European "1.234,50": =VALUE(SUBSTITUTE(SUBSTITUTE(A2,".",""),",",".")) (behaviour depends on your locale settings; check one row by hand).
Accounting negatives "(45.00)" → -45: =IF(LEFT(A2,1)="(",-VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")",""),"$","")),VALUE(SUBSTITUTE(A2,"$",""))).

6. Lookups and matching two lists

Exact match, any Excel or Sheets: =VLOOKUP(A2,Sheet2!$A:$C,3,FALSE). The final FALSE matters; omit it and you get approximate matches.
Modern: =XLOOKUP(A2,Sheet2!A:A,Sheet2!C:C,"not found").
Flexible in every version: =INDEX(Sheet2!C:C,MATCH(A2,Sheet2!A:A,0)).
Find rows missing from the other list: =ISNA(MATCH(A2,Sheet2!A:A,0)).
Lookups fail on mismatched types or hidden spaces: a number stored as text will not match a real number. Fix with tips 1 and 5 first.

General habits

Work on a copy; keep the raw export untouched.
Import CSVs via Data → From Text/CSV and set risky columns (IDs, ZIP codes, phones) to Text, or Excel will drop leading zeros and turn long numbers into scientific notation.
After cleaning, compare row counts and a column total before and after.

Full guide: clean a messy CSV in 7 fixes

Read the step-by-step article: How to Clean Up a Messy CSV in Excel or Google Sheets.

Also: Convert CSV to XLSX without losing leading zeros or mangling dates · Merge several Excel files into one · Auto-name your exported files.

Need it done, or want the one-page version?

A printable one-page CSV & Excel Data Cleanup Cheat Sheet ($7) and small custom cleanup jobs are on the services page.

Support this project

Lightning tips are temporarily unavailable.