Clean a Google Sheets Contact List: Split, Trim and Deduplicate by Email
A contact export is easier to review when names, email addresses and regions occupy separate columns. This guide follows the Google Sheets tutorial below: preserve the import, split the fields, trim ordinary extra spaces and remove repeated email records from a working copy. The demonstrated sample starts with eight contact rows and finishes with six.
Decide what counts as a duplicate
The rule in this example is one record per email address. It is not one row per name, and it is not a match across every column. Two rows can have the same email but differently spaced names or different regions. Comparing every column would leave those records separate.
Before applying that rule to your own list, inspect conflicting records and choose which occurrence should survive. The demonstration keeps the first occurrence. A shared mailbox can represent several people, and missing email addresses need separate review. Removing duplicates does not verify that an address exists or that two people are the same person.
1. Preserve the original import
- Keep the untouched import on a tab named Raw.
- Duplicate that tab and rename the copy Clean contacts.
- Perform the following changes on the copy.
Check that Raw still contains all eight sample records before continuing. A second tab provides a convenient comparison and rollback point inside the spreadsheet. For independent protection of important source files, also consider the separation and restore checks discussed in our data backup guide.
2. Split the imported column
Select the column containing the simple comma-separated records. Choose Data → Split text to columns, then select Comma as the separator. The intended result is three columns: Name, Email and Region.
Inspect several rows immediately. Each email should appear under Email, with its matching name and region on that same row. Leave room to the right before splitting so the result does not collide with existing data. If your input is a CSV file with quoted fields containing commas, use the appropriate CSV import workflow and inspect its parsing rather than assuming every comma marks a new field.
3. Trim ordinary extra spaces
Select the resulting table and choose Data → Data cleanup → Trim whitespace. In the filmed sample, this removes unwanted ordinary spaces around values and repeated spaces within names. Check a few cells afterward; a completed menu action is not enough to establish that the data is ready.
Google notes that this cleanup does not remove non-breaking spaces. If an imported value still looks padded, inspect the remaining characters before repeatedly running the same command. Keep any deliberate spaces that are part of a meaningful value.
4. Remove duplicates using Email only
- Select the complete contact table, including Name, Email and Region.
- Choose Data → Data cleanup → Remove duplicates.
- Indicate that the selection has a header row.
- Use Email as the duplicate-checking column; clear the other key-column selections.
- Run the cleanup and read the result dialog.
Selecting the complete table keeps the remaining row fields together. Choosing Email as the key determines which records are duplicates. For the demonstrated eight-row sample, Sheets removes two repeated email records and leaves six contacts. Your own expected result must come from your own source and duplicate rule.
Verify the result against Raw
- Name, Email and Region occupy separate columns.
- Ordinary unwanted spaces have been removed.
- Six sample contact records remain, with one retained record per email.
- The corresponding name and region are still aligned with each email.
- Raw still contains the original eight records.
For a list without blank emails, an optional =COUNTA(B2:B) check can count the email cells below a header in B1. It does not establish uniqueness by itself. Review the duplicate keys and the surviving rows as well as the total.
The result is a cleaned working list with its original source available for comparison. For more demonstrations with written steps and embedded videos, visit the DrivesHero tutorials hub. Google’s split, duplicate-removal and whitespace instructions explain the native controls used here.
James Kennedy is a writer and product researcher at Drives Hero with a background in IT administration and consulting. He has hands-on experience with storage, networking, and system performance, and regularly improves and optimizes his home networking setup.



