2026-10-19・Automation / Small business / Excel

Clean up a messy customer list in Excel or Google Sheets (5 formulas, 10 minutes)

Most small businesses have a customer list that grew by itself. Some names are in capitals, some are not. Emails have spaces at the end. The same customer appears three times. Mail merges break, and nobody trusts the numbers.

The good news: you can fix most of it in about ten minutes with tools you already have. Everything below works in both Excel and Google Sheets unless noted.

Before you start: make a copy of the file. Do the cleanup in new columns, check the result, and only then replace the old data.

1. Remove extra spaces: TRIM

Spaces at the start or end of a cell are invisible, but they make "Smith" and "Smith " look like two different people.

=TRIM(A2)

TRIM removes spaces at the start and end, and turns double spaces in the middle into single spaces.

Google Sheets also has a menu for this: Data > Data cleanup > Trim whitespace.

2. Fix capital letters: PROPER, LOWER, UPPER

=PROPER(TRIM(A2))   →  "jOHN smith "  becomes  "John Smith"
=LOWER(TRIM(B2))    →  for email addresses
=UPPER(TRIM(C2))    →  for postcodes, e.g. "sw1a 1aa" becomes "SW1A 1AA"

Check PROPER results by eye. It turns "McDonald" into "Mcdonald" and "O'NEIL" into "O'Neil", which may not be what the customer writes.

3. Remove hidden characters: CLEAN

Data copied from websites or other systems can carry line breaks and other invisible characters.

=CLEAN(TRIM(A2))

4. Make phone numbers consistent: SUBSTITUTE

If phone numbers are typed in different ways, strip the spaces, dashes, dots and brackets so they can be compared:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2," ",""),"-",""),"(",""),")",""),".","")

"(555) 123-4567" and "555.123.4567" both become "5551234567". Now you can sort, compare and find duplicates.

5. Find duplicates before you delete them: COUNTIF

Do not delete duplicates blindly. First, mark them:

=COUNTIF($B$2:$B$1000, B2)

Put this next to the cleaned email column. Any number above 1 means that email appears more than once. Sort by that column and look at the duplicates together. Often one row has the newest phone number and another has the newest address, and you want to merge them by hand.

Two built-in tools

• Remove duplicates. Excel: Data > Remove Duplicates. Google Sheets: Data > Data cleanup > Remove duplicates. Choose which columns must match (for example, email only). Use this after you have checked the COUNTIF results.
• Split names. Excel: Data > Text to Columns. Google Sheets: Data > Split text to columns. Splits "John Smith" into two columns at the space. Check names with more than two parts.

Put it together

A cleaned column for each field, side by side with the original:

When the new columns look right, copy them and use Paste special > Values only over the original data. Then delete the formula columns.

If you do this every month

If new messy lists keep arriving (from web forms, events or other systems), doing it by hand every time gets old. Two options:

• Save a template sheet with the formulas already in place, and paste each new list into it.
• Use a tool that does all the steps at once. We sell DataClean, an offline browser tool that cleans names, phone numbers, emails and dates, removes duplicates and keeps a log of what it changed. It runs on your computer and does not upload your list. DataClean on Gumroad

To see which other admin jobs in your business are easy to automate, try our free Small Business Automation Check.