It’s really easy to extract unique values from a comma-separated list in Google Sheets. The comma-separated list can be email addresses, names of people, product, item code or something similar.
A list without duplicates is easy to generate using the UNIQUE function in Google Sheets. The function SORTN will also do the same in a much better way! What about a comma-separated list?
To extract unique values from a comma separated list we can use the functions SPLIT and TEXTJOIN/JOIN along with UNIQUE. That’s what I am going to elaborate here in this new Google Spreadsheets tutorial.
In the below example, I have few general office supplies in Column A. In that, some stationery items are comma-separated and some are not. Also, the list
In column B, see the unique list I have extracted from Column A.
If there is no comma separated value, we can simply use the UNIQUE function as below.
=unique(A2:A)Extract Unique Values from a Comma Separated List in Google Sheets
Here is the formula that I have used to extract the unique general office supplies.
=sort(unique(TRIM(transpose(split(textjoin("|",true,iferror(split(A2:A,","))),"|")))))I have this formula in Cell B2. How this formula extracts unique values from a list that contains comma-separated strings?
Formula Explanation: Extract Unique Values From a List of Comma-Separated Values
Step 1:
The SPLIT function splits the comma separated values. It will result in a multi-column data without any comma separated values. But you must use SORT, INDEX or ARRAYFORMULA together with the Split function.
In our master formula, the outer SORT does that. Forgot to say, the IFERROR is optional in this formula. It helps to avoid errors if there is any blank cell in the list.
Step 2:
In step # 2 we want to combine the split multi-column data into one cell. I have used the TEXTJOIN function.
The function JOIN will also work. But the difference is TEXTJOIN can exclude blank cells and this helps to avoid unwanted delimiter being placed in the joined text.
I have used the pipe symbol as the delimiter to join the split text.
Step 3:
Here I am using the SPLIT function again to split the joined text and TRANSPOSE it to a single column data.
We have created a single column data from multiple columns. That’s the cumulative effect of the above Step # 2 and Step # 3 formulas.
See the single column data above. You can see that there is an extra space at the beginning of most of the values. So UNIQUE may not work well in this data.
So I have used the TRIM function to remove those unwanted spaces and finally, the UNIQUE removes the duplicates.
You can follow this approach to extract unique values from a comma-separated list in Google Sheets.
Resources:
Can this be done in the Excel online workbooks?
Hi, S,
We can do it. But remember, at present, SPLIT() is not available in Excel.
There is a workaround formula.
For formula explanation, please check this guide. In that, you can find a step#4 formula.
Wrap that formula output with UNIQUE, then SORT.
Hi Prashanth.
Please help how to have an ArrayFormula only in B2 (so B3 and B4 will get the value from the formula in B2).
List of Items
A2: Box File, Flat File, Time Cards, Box File
A3: Glue, Time Cards, Glue, Glue
A4: Pencil, Pen, Glue, Pen
Extracted Unique List (Expected result)
B2: Box File, Flat File, Time Cards
B3: Glue, Time Cards
B4: Pencil, Pen, Glue
Thank you in advance.
Hi, Zaim,
I suggest a drag-drop formula in B2.
=ArrayFormula(textjoin(", ",true,unique(trim(split(A2,",")),true)))Since you want an array formula, try the below one.
=ArrayFormula(TRIM(transpose(split(textjoin(", ",true,if(row(indirect("A2:A"&counta(left(filter(iferror(unique(flatten(
TRIM(split(substitute(if(len(A2:A99),row(A2:A99)&" "&A2:A99,),",",
", "&row(A2:A99)),","))))),iferror(unique(flatten(TRIM(split(
substitute(if(len(A2:A99),row(A2:A99)&" "&A2:A99,),",",", "&
row(A2:A99)),",")))))<>""),1))+1))-match(left(filter(iferror(
unique(flatten(TRIM(split(substitute(if(len(A2:A99),row(A2:A99)&
" "&A2:A99,),",",", "&row(A2:A99)),","))))),iferror(unique(
flatten(TRIM(split(substitute(if(len(A2:A99),row(A2:A99)&" "&A2:A99,),
",",", "&row(A2:A99)),",")))))<>""),2),left(filter(iferror(unique(
flatten(TRIM(split(substitute(if(len(A2:A99),row(A2:A99)&" "&A2:A99,),",",", "&row(A2:A99)),","))))),iferror(unique(flatten(TRIM(split(substitute(if(
len(A2:A99),row(A2:A99)&" "&A2:A99,),",",", "&row(A2:A99)),",")))))<>""),2),0)=1,"|",)&ArrayFormula(mid(filter(iferror(unique(flatten(TRIM(
split(substitute(if(len(A2:A99),row(A2:A99)&" "&A2:A99,),",",", "&
row(A2:A99)),","))))),iferror(unique(flatten(TRIM(split(substitute(if(
len(A2:A99),row(A2:A99)&" "&A2:A99,),",",", "&row(A2:A99)),",")))))<>""),
3,150))),"|"))))
Note:
1. Set to work from row 2 to 99.
2. After each comma separating the words, there should be a white space as shown in your example.
Wow, the ArrayFormula, the length… But amazing solution.
Let me test it in my real data first.
Thanks again. You’re great.
Hi, Zaim,
I have a polished version of the formula for an open range. Please visit the below page for the tutorial and sample sheet.
Remove Duplicates from Comma-Delimited Strings in Google Sheets.
Need help. How will I be able to count unique numbers separated by commas in one cell?
eg: 1234,2345,6543,3456,1234
Here I have 5 transaction numbers out of which I have 1 duplicate. How can I use the formula to calculate only the unique count of transactions in the cell?
P.S. I am using Google Sheets.
Hi, Atiq,
Assume the above comma-separated list of numbers is in cell A1. Here is the formula to use and the formula explanation.
=count(unique(TRANSPOSE(split(A1,","))))Split – split the numbers to individual cells (in columns)
Transpose – to change the orientation (numbers in columns to rows) so that we can apply Unique.
Unique – to remove duplicates
Count – to count the unique values (use Counta if the values are text)
Here is my more concise answer to the same problem:
=UNIQUE(SORT(TRANSPOSE(SPLIT(JOIN(", ", E2:E), ", ", FALSE))))For other visitors, note that TRANSPOSE needs to go around the SPLIT, otherwise your SORT/UNIQUE operations will not work.