Tidying data: trim, replace and remove duplicates
8 minutes · 4 steps · free, no sign-up
Data copied from emails, forms and other systems is rarely tidy. Extra spaces, shortened words and repeated rows all cause trouble later. Sheets has three quick tools for the most common problems, and you will use each of them on a small customer list.
- Trim extra spaces from text
- Find and replace a word everywhere
- Remove duplicate rows
- Check the result with a formula
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceThe three tools
Trim whitespace removes extra spaces at the ends and in the middle
Find and replace changes every match, e.g. Ldn to London
Remove duplicates keeps the first copy of each repeated rowThe key ideasSummary: Tidying data: trim, replace and remove duplicates
Messy data has three usual faults. Stray spaces make two identical names look different, so a lookup or a count misses them. Inconsistent spelling splits one group into several. Duplicates make totals and counts too high.
Fix them in that order: trim first, then standardise spelling, then remove duplicates. Trimming first matters, because "Asha Khan" and "Asha Khan " only count as the same row once the extra space is gone. Always work on a copy of important data, since these tools change the cells in place.
Watch out forCommon mistakes
- Removing duplicates before trimming. Rows that differ only by a space will survive. Trim and standardise first.
- Replacing too much. Replacing "St" would also change "Stuart" and "Boston". Use the match-whole-cell option, or a longer search text.
- Selecting too little for Remove duplicates. Select every column of the table. If you select only one column, rows with the same name but different details are treated as duplicates.
- Forgetting these tools cannot be re-run on new data. If new rows arrive each week, a formula such as
TRIMorUNIQUE(Adept level) keeps working by itself.
Next stepTry it in the real Google Sheets
In Google Sheets these are under Data → Data cleanup: Trim whitespace, Remove duplicates and Cleanup suggestions. Find and replace is Ctrl+H, and it can also search inside formulas and across every sheet.