In this article, you will learn how you can use a simple formula to remove duplicates from comma-delimited strings in Google Sheets.
Comma-delimited strings are lists of values separated by a comma. Removing duplicates from these lists will give you a set of unique values found in the original list.
Let’s take a look at a quick example where we might want to remove duplicates.
We have a list of orders for notebooks. Each order contains multiple color values separated by a comma. It’s possible for an order to mention a color more than once.
As the seller, you want to know the unique colors each order requires. You contemplate doing this manually, but there are now hundreds of orders on the list. It also might have been easier if the elements were sorted in alphabetical order, but that is not the case here. Is it possible to do this easily in Google Sheets?
Luckily, we have a clever formula that we can use to remove duplicates and return a unique list easily. It involves using the SPLIT, TRANSPOSE, and UNIQUE functions to treat the string as an array or a range of cells.
We might also want to remove duplicates as a form of data cleaning. A dataset might have multiple entries of the same value. Google Sheets can help clean this data for you.
Now that we know when a formula like this might be useful, let’s dive into the formula itself and how it works.
A Real Example of Removing Duplicates from Comma-Delimited Strings
Let’s look at a real example of how we can use Google Sheets to create unique lists from a comma-delimited string.
The table below has two columns. The first column has our orders, with each order written as a comma-delimited string. The second column is also comma-delimited but removes any duplicate values.
To get the values in Column B, we just need to use the following formula:
=JOIN(", ",UNIQUE(TRANSPOSE(SPLIT(A2,", "))))
How does this formula work?
First, we need to split our original string to get each individual value. We can do this with the SPLIT function. This function returns a row of cells divided based on the delimiter.
To remove duplicate values, we have to use the UNIQUE function. Since the UNIQUE function works for cells in different rows, we’ll have to transpose our SPLIT output using the TRANSPOSE function.
Finally, once the UNIQUE function returns a range of unique cell values, we can put them together again as a comma-delimited string. We can easily do this with the JOIN function. This function accepts a delimiter and a range of cells and returns a single string where each cell is separated by the given delimiter.
In the example below, we demonstrate that items with no duplicates remain the same as well.
You can make a copy of the spreadsheet above using the link attached below.
If you want to start using this formula in your Google Sheet spreadsheets, head over to the next section to learn how to do this yourself.
How to Remove Duplicates from Comma-Delimited Strings in Google Sheets
This section will guide you through each step needed to remove duplicates in your comma-delimited strings. You’ll learn how to use the formula shown in the earlier section.
Follow these simple steps to start removing duplicates:
- First, let’s select the cell which will hold the unique list. In this example, we’ll start with the first row and select cell B2.
- Type in or paste the formula for removing duplicates from comma-delimited strings. Make sure to change the value of A2 to the appropriate cell reference in your own sheet.
- Hit the Enter key to see the unique list. Observe how our example below has removed the second instance of “Red” in the string.
- Finally, we can drag down the formula to fill out the rest of the column.
Frequently Asked Questions (FAQ)
- Does this formula work for values separated by spaces?
You may change the formula slightly for this to work. We can select “ “ as our delimiter.
In the example below, we were able to remove duplicates from a phrase. Each phrase can be treated as a list, only with spaces as a delimiter.
- Will this work for numbers as well?
The formula should work with numbers as well as long as the numbers are not written with commas. For example, a million dollars may be written like this “$1,000,000” and will be separated incorrectly due to the formatting.
That’s all you need to remember to start using the Google Sheets functions to remove duplicates from a comma-delimited string. This step-by-step guide shows how easy it is to create a unique list of values using only a single clever formula.
The formula shown here is just one example of a smart way we can combine Google Sheets functions. With so many other Google Sheets functions out there, you can surely find one or two that suits your problem.
Are you interested in learning more about how Google Sheets can help you make more powerful spreadsheets? Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
