To clean messy data in Google Sheets, work on a copy, remove stray spaces with TRIM, split combined columns with SPLIT, standardize text, numbers and dates, then remove duplicates. Most problems in exported or pasted data come from the same handful of causes, and each has a quick fix.
Clean data matters because formulas, filters and lookups all depend on values matching exactly. A name with a trailing space is, to a spreadsheet, a different name.
Start with a safe workflow
Before you change anything, protect the original. Right-click the sheet tab and choose Duplicate, or keep the raw import on its own tab and build the cleaned version beside it. A simple pattern works well:
- Keep the untouched import on a tab named Raw.
- On a second tab named Clean, use formulas that reference Raw.
- When the result looks right, copy the Clean columns and use Paste special, then Values only, to turn formulas into plain values.
Working this way means a mistake costs you a minute, not an afternoon. It also makes the process repeatable when next month’s export arrives in the same messy shape.
Use the built-in cleanup tools first
Under the Data menu, Sheets has a Data cleanup option with several one-click tools. It can suggest fixes, remove duplicates, trim whitespace and, in some versions, group similar values. These tools change the data in place, so run them on your working copy.
- Trim whitespace removes leading, trailing and repeated spaces in the selected range.
- Remove duplicates lets you choose which columns decide whether a row is a duplicate and whether the first row is a header.
- Split text to columns breaks a column at a separator such as a comma, space or semicolon.
For a one-off job these are often all you need. When the same cleanup happens regularly, formulas are better because they run again automatically.
Formulas to clean messy data
Spaces and invisible characters
=TRIM(A2) removes extra spaces. If a value still will not match, it may contain a non-breaking space, common in data copied from web pages. TRIM does not remove those, but SUBSTITUTE can: =TRIM(SUBSTITUTE(A2, CHAR(160), " ")). The CLEAN function removes non-printing characters such as line breaks from imported text: =CLEAN(A2).
Capitalization
PROPER, UPPER and LOWER standardize case. =PROPER(TRIM(A2)) turns ” jOHN smith ” into “John Smith”. Be aware that PROPER capitalizes every word, so names like “McDonald” become “Mcdonald” and acronyms lose their capitals. Check the results rather than trusting them blindly.
Splitting and combining
=SPLIT(A2, ",") spreads “Smith, John” across two cells, though the second will still have a leading space, so wrap it as =ARRAYFORMULA(TRIM(SPLIT(A2, ","))). To go the other way, join cells with the ampersand or with TEXTJOIN, as in =TEXTJOIN(" ", TRUE, B2:C2).
Removing unwanted characters
SUBSTITUTE replaces specific text: =SUBSTITUTE(A2, "$", "") strips a dollar sign. For patterns rather than fixed text, REGEXREPLACE is more flexible. =REGEXREPLACE(A2, "[^0-9]", "") keeps only the digits, which is handy for phone numbers. Wrap the result in VALUE when you need a real number.
Fix numbers and dates that are really text
A number that is left-aligned in its cell is often stored as text, so SUM ignores it. Try =VALUE(A2) to convert it, or multiply by one with =A2*1. For dates, =DATEVALUE(A2) converts text that Sheets recognizes as a date into a true date serial number, which you then format through Format, Number, Date.
Dates written as day, month and year can be misread depending on your spreadsheet’s locale, so check a few rows where the day is above 12 to confirm they were read correctly. If the order is wrong, split the text with SPLIT and rebuild it with the DATE function: =DATE(year, month, day).
Find and remove duplicates carefully
The UNIQUE function returns a list without repeated rows: =UNIQUE(A2:C100). It leaves your source untouched, which makes it safer than deleting rows. To see duplicates rather than remove them, use conditional formatting with a custom formula such as =COUNTIF($A:$A, $A1)>1 to highlight repeated entries so you can decide what to do.
Think about what makes a row a true duplicate. Two customers with the same name may be different people, so match on something more specific, such as an email address.
Prevent the mess next time
Cleaning is easier when the data comes in tidy. Where you control the input, use Data validation, under the Data menu, to restrict entries to a drop-down list, a number range or a date. Use one column per fact, avoid merged cells in data tables, and keep one header row. For shared sheets, add a note saying what each column should contain.
Tidy data also makes the next steps easier. Once your values are consistent, lookups are reliable, which we explain in our VLOOKUP and XLOOKUP guide, and the functions above build on the basics in our formulas for beginners. If you clean the same file every week, Google Apps Script can automate the routine, and more ideas live in our spreadsheets section.
Where to start
Pick one messy file you use often, duplicate it, and clean one column at a time: trim, fix case, then fix types. Write down the steps you took, because that list is the start of a repeatable process you can reuse each time new data arrives.
Frequently asked questions
Why does TRIM not remove all the spaces in my data?
The spaces may be non-breaking spaces, which TRIM ignores. Use SUBSTITUTE with CHAR(160) to replace them with ordinary spaces first.
How do I remove duplicates without deleting my original data?
Use the UNIQUE function in another range, which returns a de-duplicated list and leaves the source untouched.
Why does SUM ignore some of my numbers?
Those values are probably stored as text. Convert them with VALUE or by multiplying by 1, then check the alignment and format.
Can I clean a whole column with one formula?
Yes. Wrap the formula in ARRAYFORMULA and refer to a range such as A2:A, and one cell will fill the whole column.
