How to Remove Duplicates within a Cell in Excel | Practical Examples

In this Excel video tutorial, we gonna see How to Remove Duplicates within a Cell in Excel with Practical Examples. We can use the formula below to separate the texts of the cell, remove the symbols or delimiters, remove repeated values and the group everything together again.
The functions are, textjoin formula, unique formula, textsplit formula.
=TEXTJOIN(", ",TRUE,UNIQUE(TEXTSPLIT(A2,{", "," - "," | "," * "}),TRUE))
Components of the Formula
1. TEXTSPLIT(A2,{", "," - "," | "," * "}):
This function splits the text in cell A2 into an array of substrings using multiple delimiters: ", ", " - ", " | ", and " * ".
For example, if A2 contains "apple, banana - orange | pear * grape", this function will split it into {"apple", "banana", "orange", "pear", "grape"}.
2. UNIQUE(..., TRUE):
The UNIQUE function takes an array and removes duplicate values. The second argument TRUE ensures that the function treats the array as containing text values, making the function case-sensitive.
If our array from the previous step contains duplicates like {"apple", "banana", "orange", "banana", "pear", "grape"}, the UNIQUE function will return {"apple", "banana", "orange", "pear", "grape"}.
3. TEXTJOIN(", ", TRUE, ...):
The TEXTJOIN function joins the elements of an array into a single text string, with a specified delimiter ", " in this case. The second argument TRUE tells Excel to ignore empty cells.
For the array {"apple", "banana", "orange", "pear", "grape"}, TEXTJOIN will return "apple, banana, orange, pear, grape".
Putting It All Together
- TEXTSPLIT splits the text in cell A2 into an array of substrings based on the specified delimiters.
- UNIQUE removes any duplicate values from this array.
- TEXTJOIN combines the unique substrings back into a single text string, separated by commas.
#JopaExcel #Dashboard #Excel