Split text, remove duplicates and trim whitespace in Google Sheets

Quick answer

Split a delimited text column into empty adjacent columns, trim ordinary extra spaces, then remove duplicates using the full intended record key. Preserve the original first: splitting, trimming and deduplication change data, not just its appearance.

Prepare a bounded cleanup job

Use native Google Sheets on a computer with edit permission. The steps below use desktop menus, not mobile controls. This is a small roster-import cleanup, not a tutorial on formula-based joins or combining multiple source tabs.

In a private file, enter the following text in A2:A6 with A1 = Raw record. Type the literal entries below without backticks. For A2 only, manually add two ordinary ASCII spaces before Alex and two after East. Those spaces are described explicitly rather than represented by the code formatting.

• A2: Alex,East, with the four added spaces described above
• A3: Blair,West
• A4: Alex,East
• A5: Casey,East
• A6: Alex,West

Keep this raw tab unchanged. Make a working copy before cleanup. On the working tab, reserve B:C as empty output space; do not place notes immediately to the right of the raw records. Your target is four unique person-region pairs. Alex/East and Alex/West are different valid records, even though the person name repeats.

Split and inspect the fields

Select A2:A6 on the working tab and choose Data > Split text to columns. In the Separator control, select comma rather than relying on automatic detection. The first field remains in column A and the second occupies B. The empty destination was intentional: splitting into occupied columns risks disturbing existing data.

Replace A1 with Person and enter Region in B1. Confirm that all five source records produced exactly two fields. A6:B6 should be Alex and West, not part of another row. If a source contains a quoted comma inside a name or an inconsistent delimiter, stop and resolve that different import format rather than assume a simple split is sufficient.

Trim before deciding which rows repeat

Select A2:B6 and choose Data > Data cleanup > Trim whitespace. Inspect A2 and B2 against A4 and B4. With the ordinary spaces supplied in this fixture, both rows should now contain Alex/East.

The cleanup removes leading, trailing and excessive spaces. Google’s documentation specifically excludes non-breaking spaces. If a web-pasted label still looks padded, identify the character before repeating Trim whitespace or deleting all internal spaces. A legitimate two-word name must not be collapsed into one word.

Remove exact record duplicates

1. Select A1:B6 explicitly, including the two headings.
2. Choose Data > Data cleanup > Remove duplicates.
3. Indicate that the selected data has headers.
4. Include both Person and Region in the duplicate comparison.
5. Click Remove duplicates and inspect the result rather than relying only on the dialog’s count.

The expected unique set is Alex/East, Blair/West, Casey/East and Alex/West. One repeated pair is removed: five records become four. There should be three distinct people but four valid person-region pairs. If only three rows remain because you compared Person alone, the operation wrongly discarded one of Alex’s regional assignments.

Sheets considers identical values with different letter cases, formats or formulas duplicates. Do not use this tool as a case-sensitive identifier audit without first defining an appropriate key and comparison method.

Check a changed record and retain recovery evidence

In another copy of the raw fixture, change A4 from Alex,East to Alex,North, then repeat the same split, trim and two-column comparison. Now all five person-region pairs are distinct. No pair should be removed. This boundary test shows that the selected comparison columns, not visual similarity, determine the outcome.

Keep the verified four-pair result and the original raw tab separately. After online saving, reopen both and compare the final pairs with the five original lines. If cleanup went wrong, restart from the raw copy; recreating deleted records from memory is not a reliable recovery procedure.

For changing only which records you see, use sorting and filtering rather than deleting repeats. If codes need leading zeros preserved, address that during CSV import before running cleanup.

Official references

• Split text, remove duplicates, or trim whitespace - Computer - Google Docs Editors Help
• Create, view, or download a file - Computer - Google Docs Editors Help

Documentation basis: first-party help snapshots captured 20 October 2026. Fixture arithmetic and stated changes were checked independently. No native Google Sheets session, Excel round trip, screenshot, sharing action or export was executed for this article.