Do you want to extract unique values from a comma-separated list in Google Sheets? Whether you have a list of email addresses, names, product codes, or any other type of data, it’s actually quite easy to accomplish with just a few simple functions. In this article, I will show you how to use the SPLIT and TEXTJOIN/JOIN functions along with UNIQUE to extract unique values from a comma-separated list in Google Sheets.
Extracting Unique Values in Google Sheets
To begin, let’s take a look at an example. In Column A, we have a list of general office supplies, some of which are comma-separated and some are not. Additionally, the list contains duplicate values. Our goal is to extract the unique values from this list and display them in Column B.
To achieve this, we can use the following formula in Cell B2:
=sort(unique(TRIM(transpose(split(textjoin("|",true,iferror(split(A2:A,","))),"|")))))
Now, let’s break down the formula and understand how it works.
Formula Explanation: Extract Unique Values From a List of Comma-Separated Values
Step 1:
The SPLIT function is used to split the comma-separated values into multiple columns. This results in a multi-column data range without any comma-separated values. In our formula, the outer SORT function is used to sort the values. Additionally, the IFERROR function helps to avoid errors if there are any blank cells in the list.
Step 2:
In this step, we want to combine the split multi-column data into a single cell. We achieve this by using the TEXTJOIN function. The advantage of using TEXTJOIN instead of JOIN is that it can exclude blank cells, preventing unwanted delimiters from being placed in the joined text.
Step 3:
In the final step, we use the SPLIT function once again to split the joined text into separate cells. However, this time we transpose the result to convert it into a single-column data range.
By following these steps, we have successfully created a single-column data range with unique values extracted from the original comma-separated list. To ensure accuracy, we use the TRIM function to remove any unwanted spaces at the beginning of the values, and then the UNIQUE function removes any duplicate values.
You can easily apply this approach to extract unique values from any comma-separated list in Google Sheets. It’s a quick and efficient way to clean up your data and focus on the unique entries.
For more helpful tips and tutorials on using Google Sheets, visit Crawlan.com.
Conclusion
In this article, we’ve explored how to extract unique values from a comma-separated list in Google Sheets. By using a combination of functions like SPLIT, TEXTJOIN/JOIN, and UNIQUE, you can easily generate a list without any duplicates. This technique is useful for a variety of scenarios, whether you’re working with email addresses, names, or any other type of data. Remember to follow the provided formula and steps to ensure accurate results.
If you found this article helpful, be sure to check out the additional resources listed below for more Google Sheets tips and tricks:
- Replace Multiple Comma Separated Values in Google Sheets
- How to Count Comma Separated Words in a Cell in Google Sheets
- Sum, Count, Cumulative Sum Comma Separated Values in Google Sheets
Happy spreadsheeting!
