Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Count Number Of Unique Values In A Single Cell Separated By Commas

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi, probably a very easy one here, im looking to count the unique number of values within a single cell string which are separated by commas


Cell A1 contains: 1,2,3A33,444,2,1,16

The resulting formula would give a count of 5 unique value within cell A1 as values '2' and '1' are repeated twice within the cell and would therefore be excluded.

Any help would be most appreciated, thank you!

View Answers     

Similar Excel Tutorials

Count the Number of Unique Values in a List in Excel
Use a formula to count the number of unique values that are contained within a list in Excel. Steps to Count the Nu ...
Get the First Word from a Cell in Excel
How to use a formula to get the first word from a cell in Excel. This works for a single cell and an entire list or ...
Count The Number of Words in a Cell or Range of Cells in Excel - UDF
Count words in cells with this user defined function (UDF). This UDF allows you to count the number of words that ...
How to import Text Files (CSV) into Excel
Text files in CSV format are one of the easiest ways to store and transfer data as it is one of the most compatible ...

Helpful Excel Macros

Count The Number of Words in a Cell or Range of Cells in Excel - UDF
- Count words in cells with this user defined function (UDF). This UDF allows you to count the number of words that are w
Count The Number of Words in a Cell or Range of Cells in Excel - With User-Specified Delimiter / Separator - UDF
- UDF to count the number of words in a cell or range with a user-specified delimiter. This means that you can tell the f
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to

Similar Topics

Is it possible to have a formula that can count the amount of values in a single cell?


Cell A2 contains "C1, C2, C3, D1, D4, D5"
is there a formula i can put in Cell B2 that will count how many values there are in A2?

so cell B2 would then say "6"

Every value is separated by a comma and a space exactly as above (if that helps)


Sorry if that's a bit long winded...

Here's how it breaks down.

If I have, in one cell (call it D1):


and in a lookup table on another sheet:
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU E

I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...



Does that make sense or should I resubmit?

I've tried a few different ways but can't come up with anything...

There's more to the project, but this bit will help me get it off the ground and hopefully I'll be able to string the rest together.

Cheers guys! Great forum!

Good morning everybody.

I have a range of cells containing names of scientists separated by a pipe delimiter.

For example
Propp; Michael B.|Propp; David L.
De Gaudenzi; Riccardo|Viola; Roberto|Elia; Carlo
Benedicto Ruiz; Francois-Xavier|Lopriore; Mario|Bella; Luigi

I needed to count the number of scientists in each cell, assuming they are separated by a pipe delimiter. So, the formula would need to return the value 2 for the first of my examples, and 3 for the second and the third.

It needs to therefore count the number of delimiters and return the value of that count + 1 - i.e. if there are two delimiters in the string, then there must be three items, and so on.

Have any ideas?

Thank you so much!

I have a sheet with many entries in each row. Some match others don't...i want to be able to highlight the populated cells in the row, and concatenate a string of unique values separated by commas and ready to paste into another workbook.

any takers?



I am after something that seems simple, but I can't figure out a way to do it.

If I have a single cell containing a list of values seperated by a comma (ie a csv list in a cell), how do I count the number of values. I figure it must be by counting the commas somehow and then adding one as the last doesn't have it, but there is no way to tell how many there will be.

My cell contents look like this
18911, 18801, 18825, 18482, 18941

so I want to have another cell return the number 5.

Does anyone have any ideas?


Hi, I am wondering if it is possible to count the number of unique values in two columns greater than some other value. To clarify, if I have two columns (A and B) with the values listed below.


3 6
2 3
4 2
6 8
5 10

I want to count how many unique values between the two columns are less than 5. In this case, there should be 3 (2,3 and 4 from column A as well as 2 and 3 from B fulfill this condition). Is there any way to do this efficiently without using VBA? I have searched this forum and a few others and have seen SUM(IF(FREQUENCY(...)) being used in single column counts, but I don't know how to adapt this to multiple columns of data. Thanks.

I have been using this function to count the number of unique text values in a data set:

It works great if I want to count number of unique text values overall. However, I want to count the occurences of unique text values if they meet specific criteria. (Like a countifs function would if it could count unique text).

Any ideas?


I need help performing a lookup function with inputs in one cell separated by commas.

I have a single cell with a given number of initials (my lookup values):


I have a table with a cost assigned to each of these initials (my lookup array):

RY 200
AH 200
NR 400

I would like to perform a vlookup off the cell containing the comma separated values to generate an output such as 200, 200, 400.

I would also like the function to be flexible enough to provide outputs based on any given number of inputs in the cell such as
RY, AH, NR, WG or

Thank you so much for anyone's help on this!


I've tried various formulas to count the number of unique values, but I haven't found one that accounted for my second criteria. What I'm trying to do is to count the number of unique Type #'s but only for the ones that will be included. I hope my request makes sense, but if not just let me know. Thanks.

I have an excel sheet that has multiple rows for one PO number. I've created a row at the top to calculate all of the unique PO numbers in the sheet. However, I want to create another column that will give each unique PO number it's own ID number starting at 1. For instance:

Column B (PO Number)
4 - Counts unique values in B

Then in Column C I'd want
1 (unique number for 219)
1 (unique number for 219)
1 (unique number for 219)
2 (unique number for 220)
2 (unique number for 220)
3 (unique number for 221)
4 (unique number for 222)
4 (unique number for 222)

Any ideas? If I'm not making sense just say so and I'll try to clarify.

What I have a cell with values separated by , (commas).
Ex: Apple, Orange, Pea

The number of values will vary per worksheet and what I would like to do is separate out those values into individual cells. I know i know - text to columns... :o Slow down - there is data in the adjacent cell that I don't want to overwrite.
What I would like to do is count the occurances of the , (comma separating the values) and insert that number+1 columns to the right of it.

If that wasn't enough - I would then like to use a vlookup to look up those indiviual results and return another value (from a different worksheet).

So I have:
Cell A1=Banana, Apple, Orange;
Cell B1=Data I Need

What I want:
Cell A1=vlookup(banana);
Cell B2=vlookup(apple);
Cell C3=vlookup(orange);
Cell D4=Data I need (from cell b2...ZZ2)

Any suggestions on where to begin - is there a count feature that will count indiviudal occurances of the comma in the cell that I could then use to get the number of columns to insert?

Thanks for any and all help

Say in Cell A1, I have numbers separated by commas, like this:

123, 435, 456, and so on, many, like over 100 like this in a single cell,

I would like to paste those values so that I have 100 values spread over single cells, they would go like this:

B1: 123
B2: 435
B3: 456

Has anyone figured this out ?

Manual way and VBA examples would be nice, thanks for your time!

Dear Most Amazing Excelrs In The World,

I am trying to count the unique values that are greater than 30 in the range A1:A5. The values a


The answer should be 2 because 45 and 90 would be counted.

I have successfully created a few solutions with a Pivot Table and Advanced Fliter, but when I try to do one Array formula in one cell, I have no luck.

Any ideas about how to count the unique values that are greater than 30 in the range A1:A5 with an array formula?

Need a formula in Col AF to count the number of unique digits, without counting duplicate digits. The 2-digit values are to be treated as 2 separate values. If the value has a leading zero, (example AA1= 06, don't count zero. All 2-digit values 10,20,30, count the zero).
The unique digits below are 1,2,3,6,8 for a total of 5.
06 18 23 28 32 5

UNIQUE VALUES Z AA AB AC AD AE AF 1 06/11/10 06 18 23 28 32 5 2 06/10/10 01 14 20 22 35 6 3 06/09/10 02 09 10 17 20 6 4 06/08/10 11 15 24 27 34 5 06/07/10 01 08 18 25 27 Excel 2007

I know that to join the contents of several cells into a single one (concatenate is the technical word), all it takes is the function & and that to have values separated by a comma and a space all you have to do is:
a1&", "&a2&", "&a3&", "&a4

My problem is that sometimes some of these cells are empty so I end up with some values separated by commas, empty spaces and more commas without more values in between.

What would be the way to concatenate only the non-empty cells?



Please advise on how to count unique date values (e.g., 2/4/07, 2/4/07, 2/5/07, 2/6/07 = 3 unique values).

I'm working on a spreadsheet which has a date column with duplicate dates. I need to count the number of unique dates.

Your help is greatly appreciated!!!



Is it possible to have a macro count the number of unique entries (excluding blanks) in each column of an Excel database and then enter each of the values (the unique counts) two cells above each column?

What I am trying to do is have a macro do the unique count whenever the worksheet changes instead of creating array formulas that would slow down recalc time.



I am trying to extract all unique values as well as the quantity of repeated values from a list of non-consecutive cells. Basically, I need to create a parts list based on part numbers which are created through a series of selections. The data is then concatenated into a single cell, and then all unique items are listed and quantified. I am able to generate the list, but can't figure out how to get a list of the unique items and quantities extracted into a separate list without the blank cells in between.



I have given a search on this forum for this matter but I didn't find any solution.

I have 4 coloumns (IN, OUT, IN+OUT, IN-OUT) and N rows. I need to know how can I be able to make a SUM & DIFFERENCE between the IN and OUT values. These values, both in IN and OUT, come into a single cell, separated by ";". Now, you'll probably tell me it's wrong to have multiple values in a single cell but I can't work any other way because the NUMBER of these VALUES is not constant in order to be able to separate them in each coloumn.

Also, the NUMBER of these multiple values in a single cell can be up to 50, 50 values. And each value can have up to 7 digits.

I have linked to you a sample of what I'm trying to do.

I know little about VBA programming but I'm willing to do some damage.
I would really appreciate it.
Thank you.

Good morning!

I am new to the forum and have a problem that I need help to solve. I am not proficient with formulas, macros and advanced excel stuff.

I have a sheet with values in a cell separated by commas. I need to change those values to another set of values given to me and retain that they are separated by commas.

I have attached an example. Sheet named "Data" are my original values. Sheet named "Lookup table" is such. Sheet named "Result" is what I need my answer to be.

I would appreciate any assistance. Thank you!

Hi Guys ...

Have a worksheet 30 columns and 4000 rows. On certain columns i need to be able to count the number of unique fields but the entries have common and unique identifiers.

For Example how can I count the number of fields with PXXXX followed by the number of PYYYY. I only need to be able to count each of these unique P numbers once.

Thanks in advance


I have read a lot in here about counting unique cells. However, I need to completely disregard duplicate values when counting.

For example, column A is a list of customer numbers. Each row of data represents an order they have placed. I need to count the number of customers who have only made one order. With the array formulas I am reading about for counting unique values, even the customers who have ordered more than once will be counted once.

Column A

Using the "count unique values" formula I have seen on many other threads, the count for the above data would be 4. I need to completely disregard the "655" and end up with a total count of 3 completely unique values.

Any help on this would be greatly appreciated.
Please let me know if I can clarify anything, or if you need any more information.

Hey everyone,

Here is my situation. I am trying to count the number of unique customer in a set of data but I need to have some criteria involved to filter down. Here is an example of the data I have:

Column A - Customer Number (ex. 1,2,3,4,5,etc.)
Column B - Location (ex. MN,TX,MI,WA,etc.)
Column C - Month (ex. Jan, Feb, Mar,etc.)

The formula I've been using to count the unique values is:


This works fine for the total number of unique customers but I need to know for example the number of unique customers in MN in Jan. Is there a variation to this formula that would solve that or a different formula all together? Any help would be much appreciated. Thanks!

Hello all,

I'm looking for a method to count the number of unique values (ID Number) within a selected date range. I know how to count unique values within a general range (=SUM(IF(FREQUENCY($A:$A,$A:$A)>0,1)), but the additional variable of date range is stumping me something fierce.

Here's some demo data:

Please Login or Register  to view this content.

I have a spreadsheet with a list of identifing unique number (some with alphanumeric). I need to ensure that the number keyed into the next cell down is always unique and none of the above numbers are repeated?
I had thought of doing something with a inserting new column and inserting a count formula but am not sure how to go about this?

Any help would be appreciated?