If you’re working with lists inside cells—like names or items separated by commas—and want to clean them up by removing duplicates, Google Sheets offers a couple of neat formula-based solutions.
This guide shows you how to remove duplicates from comma-delimited strings in individual cells or across multiple rows. Whether your delimiter is a comma, pipe (|), hyphen (-), or even a newline, these formulas will help you clean your data while keeping it neatly formatted as a string.
When Would You Need This?
Imagine a cell with the value:
Apple, Orange, Banana, Apple
You want it to become:
Apple, Orange, Banana
The problem is, UNIQUE doesn’t work directly on text strings like this—it only works on actual cell ranges. That’s where a combination of SPLIT, TRIM, UNIQUE, and TEXTJOIN comes in handy.
Let’s walk through the two main solutions.
Remove Duplicates from Comma-Delimited Strings: Non-Array Formula
If you’re only working with one cell (say, A2), here’s a straightforward formula to remove duplicate values:
Formula (for single cell A2):
=ArrayFormula(IFERROR(TEXTJOIN(", ", TRUE, UNIQUE(TRIM(SPLIT(A2, ",")), TRUE))))How it works:
SPLIT(A2, ",")– Splits the string into separate values.TRIM(...)– Removes leading/trailing spaces.UNIQUE(..., TRUE)– Removes duplicates from the split values.TEXTJOIN(", ", TRUE, ...)– Rejoins the unique values into a clean, comma-separated string.IFERROR(...)– Handles empty cells or invalid inputs gracefully by preventing errors.ArrayFormula(...)– Enables TRIM and other functions to process arrays of values.
To use this across multiple rows, you can drag the formula down.
Want to Use a Different Delimiter?
No problem! Replace the delimiter in both SPLIT and TEXTJOIN.
For example, to remove duplicates from a pipe-delimited string (|):
=ArrayFormula(IFERROR(TEXTJOIN("| ", TRUE, UNIQUE(TRIM(SPLIT(A2, "|")), TRUE))))Array Formula to Remove Duplicates from Comma-Separated Strings
If your data spans multiple rows in A2:A, and you don’t want to drag the formula down manually, use this dynamic version with a Lambda Helper Function (MAP + LAMBDA):
Formula:
=ArrayFormula(MAP(A2:A, LAMBDA(r, IFERROR(TEXTJOIN(", ", TRUE, UNIQUE(TRIM(SPLIT(r, ",")), TRUE))))))Why this is better:
- No need to drag the formula down.
- Automatically spills clean, duplicate-free results into column B.
This formula applies the same logic from the non-array version to each row of the range and returns the cleaned result in a single step.
Which One Should You Use?
| Scenario | Recommended Formula |
|---|---|
| One-off cleanup in a single cell | Non-array formula |
| Multiple rows of lists | MAP + LAMBDA array formula |
| Custom delimiters | Modify the delimiter in SPLIT & TEXTJOIN |
