Remove Duplicates Within a Cell in Excel

To remove duplicates in a cell in Excel, you split the cell's text into separate items, keep one of each, and join them back together. Three functions do it in one formula: TEXTSPLIT, UNIQUE and TEXTJOIN. This guide is for anyone with cells like "red, blue, red, green", such as tags, skills, email lists or product codes exported from another system, and it also shows a method for older Excel.

The formula to remove duplicates within a cell#

Here is the whole formula for a cell where items are separated by a comma and a space:

=TEXTJOIN(", ",TRUE,UNIQUE(TEXTSPLIT(A2,,", ")))

Type the formula in B2 and fill it down (Windows: Ctrl+D; Mac: ⌘D). Each row is cleaned on its own, and the result is a single cell of text, so nothing spills. The original order is kept: each item stays where it first appeared.

This needs Microsoft 365 or Excel 2024, because TEXTSPLIT is a new function. UNIQUE needs Excel 2021 or later, and TEXTJOIN needs Excel 2019 or later.

How the formula works, step by step#

Read it from the inside out. Each function does one job:

1. TEXTSPLIT(A2,,", ") splits the text at every comma and space. The empty second argument and the ", " in the third place mean "split into rows", so you get a list down a column: red, blue, red, green, blue. The TEXTSPLIT function guide covers every argument.
2. UNIQUE(...) keeps the first copy of each item: red, blue, green. See the UNIQUE function guide.
3. TEXTJOIN(", ",TRUE,...) joins the list back into one cell with a comma and a space between items. TRUE tells it to skip empty items. The TEXTJOIN function guide explains both settings.

For a deeper look at the first step, read how to split text with TEXTSPLIT.

Why UNIQUE doesn't remove anything (rows vs columns)#

If you write TEXTSPLIT(A2,", ") with one comma, the formula returns the text unchanged. That's not a bug. TEXTSPLIT with only a column delimiter returns the items across one row, and UNIQUE compares whole rows by default. One row is always unique, so nothing is removed.

There are two fixes, and both give "red, blue, green":

=TEXTJOIN(", ",TRUE,UNIQUE(TEXTSPLIT(A2,,", ")))
=TEXTJOIN(", ",TRUE,UNIQUE(TEXTSPLIT(A2,", "),TRUE))

The first splits into rows. The second keeps the row but sets UNIQUE's second argument, by_col, to TRUE so it compares columns. The first is easier to remember. The article on the UNIQUE function explains by_col in more detail.

How to handle uneven spaces and different separators#

Real data is rarely tidy. If some items have a space after the comma and some don't, split at the comma alone and clean each item with TRIM:

=TEXTJOIN(", ",TRUE,UNIQUE(TRIM(TEXTSPLIT(A2,,","))))

For "red,blue, red" this returns red, blue. Without TRIM, " red" (with a space) and "red" would count as different items.

Change the delimiter to match your data:

If you also want a different separator in the result, change the first argument of TEXTJOIN. For example, TEXTJOIN("; ",TRUE, ...) joins with semicolons. Empty items from double commas are skipped because TEXTJOIN's second argument is TRUE; see how TEXTJOIN ignores blanks.

Sort, count or keep only single items#

Small changes to the same formula answer related questions. All examples use "red, blue, red, green, blue":

The last one uses UNIQUE's third argument, exactly_once. Set to TRUE, it keeps only items with a single copy.

Remember that UNIQUE is not case-sensitive. "Red, blue, RED" becomes "Red, blue": RED is treated as a repeat of Red, and the first spelling wins.

How to keep the formula short with LET#

If the split part appears more than once, LET gives it a name so Excel works it out only once. LET needs Excel 2021 or Microsoft 365:

=LET(items,TRIM(TEXTSPLIT(A2,,",")),TEXTJOIN(", ",TRUE,UNIQUE(items)))

Read it as "let items be the trimmed pieces of A2, then join the unique items". The result is the same as the longer formula, but it is easier to read and to change later. For example, to sort as well, change only the last part to SORT(UNIQUE(items)).

When you're happy with the result, you may want plain text instead of a formula, for example before you delete the original column. Copy the results, then paste them back as values (Windows: Ctrl+Alt+V, then V; Mac: ⌘+Control+V, then choose Values).

How to remove duplicates in a cell in older Excel#

Without TEXTSPLIT, do it in steps with built-in tools:

1. Copy the cell to a spare sheet.
2. Use Data > Text to Columns, choose Delimited, tick Comma, and click Finish. Each item goes into its own column.
3. Copy the items, right-click an empty cell and choose Paste Special > Transpose to turn them into a column.
4. If a space followed each comma, the pieces now start with a space. Clean them with TRIM in a helper column and paste the results as values, or " red" and "red" will count as different items.
5. Use Data > Remove Duplicates on that column. The guide to removing duplicate rows shows this step in detail.
6. Join the result back with TEXTJOIN in Excel 2019, or with & in older versions.

This works for a few cells but not for hundreds. For large lists in older Excel, Power Query (Data > From Table/Range) can split, remove duplicates and combine again in a repeatable way.

Going the other way, if you want to collect matching values from many rows into one cell without repeats, see how to list matches in one cell.

Practise it#

This formula is three modern functions working together, so practise each one. Practise the UNIQUE function in a live spreadsheet, then practise modern Excel functions like TEXTSPLIT and practise joining text with TEXTJOIN. Each lesson checks your formula as you type it. Then try the tags exercise on this page.