Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Counting Numbers Within A Cell Separated By Comma

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

Hi, How can we count numbers in a cell that are separated by commas?

Eg., 12,13,14,15,[16]

The count result would be 4 in this case, excluding the one in the brackets.


View Answers     

Similar Excel Tutorials

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 ...
Make a Drop Down List Menu in a Cell in Excel
I'll show you how to make a drop down list menu in a cell in Excel.  This allows you to have a list appear when yo ...
Quickly Combine a List of Values and Put a Delimiter Between Each Value in Excel
How to combine a list of data into one cell while putting a delimiter between each piece of data. This tutorial us ...
Count the Number of Cells that Contain Specific Text in Excel
How to count the number of cells that contain specific text within a spreadsheet in Excel. I'll show you how to cou ...

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
Generate a Non-Repeating List of Random Numbers in Excel - UDF
- Generate a series of non-repeating random numbers in Excel with this UDF (user defined function). This is a great funct
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
Get the Number(s) Out of a Cell that Contains Both Text and Numbers in Excel - UDF
- This free Excel UDF allows you to get the numbers out of a cell that contain both text and numbers. This is a great fun

Similar Topics

Here are five sample cell entries:
G2 = 4
J2 = Blank (Empty)
M2 = 13,19,20
P2 = 2,10
S2 = 29

always numbers, separated by commas if more than one entry

I need to count the number of numbers

in the 5 sample cells listed above the count would be 7

there are actually 12 cells in each range to be counted and as in the sample they are 3 columns separated. I could revamp the entire spreadsheet to make them adjacent if necessary but I want to change the users spreadsheet as little as possible. I'm only trying to automate some data entry for her.

Good afternoon,

I have a problem, i need to count the number times a given number occurs in a range of cells

Screen Shot 2013-09-01 at 12.11.04 PM.jpg

The problem lies in the fact that each cell there are two numbers separated by a comma. The first represents a activity code and the second is a user codes. I need to be able to pull out totals for each.

I am by no means an excel master, so please make this as simple as possible

Thank you in advance


I have a range of cells counting the following data.


















All I'd like to be able to do is count the number of times a number appears in the range. ie 1 appears 9 times etc. There maybe more than 2 numbers in a cell max of 4 numbers separated by comma's. Min cell value is 1, max is 20. So the end result should be a column containing the numbers 1 to 20 and a column next to it of how many times each number appears in the range containing the formula. It would look something like below for the data above...











































I am trying to open a comma separated value spreadsheet document and I can't get the information contained in the document to appear as a plain text file separated by commas

every item in the spreadsheet is in its own cell and i am trying to use that data to appear in a text file of some sort with each item separated by a comma

any help would be greatly appreciated!


I want to be able to count the how many numbers separated by commas are in a given cell. Is there a function that will do that for me?

If the cell contains 4,6,9,11,15 I want the function to return a value of 5.

Thanks for any help.

Hi all

I would like to count the words separated by a "-"
Right now I have this below


But what I would like is for the formula to count the WORDS and Numbers in a cell that are separated by a
so the answer would be 8

What am i doing wrong?


I have three columns. Column A contains a number, column B contains bunch of
numbers separated by comma corresponding to the number in column A. In the
column C, I want to count the numbers in col. B separated by comma. Example:

Column A Column B Column C
1200 2,3,4,8 4 (there are four numbers in col.B)
1300 1, 12-15 5 (there are five numbers in col.B, notice
the range has a dash between 12 and 15 in Col.B - this is an addition to
previous question which was answered very well and I appreciate that)

Hi ... it's me again ..

A1 is a cell where I want to enter a few numbers, separated by commas ... e.g. 3,14,27,31
A2 is a cell containing a number which may or may not be one of the numbers in A1.
A3 does something if A2 is true.

I started by using the FIND() function, which seemed to work until I realised that if searching for a "1", it couldn't differentiate between 1 and 14. I though I could solve this problem by searching for the number surrounded by commas, as in ","&A2&",". This worked too, except it ignored the numbers at the start and finish of the cell being queried (A1).

Uh oh ... looks like a VB application.

Can anyone help out?

Regards ... G-fer.

Hi All,
Thank you in advance for your help.
I have a string of invoice numbers, separated by commas. I am trying to figure out how to have a macro look in the string on column G, do a vlookup for each invoice # (separated by a comma), and then put the total in column H.

I have attached an example spreadsheet. Column G:H are what I need to look for and paste results. Columns A:B are what the vlookup references.



I would like your help with this scenario. need to set up a spread sheet where i can plug in an unlimited number of serial numbers in a colunm. However, I want a funcition that will give me the same serial numbers, separated by a comma in another cell. I posted an attachement so you see what I mean. I also need to be able to share the spread sheet and have others use it at work. Please help.

Thank you!


Example Serial.xlsx

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!

Hi there,

I have a row of numbers all in different cells (E5:AG5).

Is it possible to merge all these numbers into one cell separated by a comma?

24 40 41 42

24, 40, 41, 42

Many thanks,


Every body
I have 50 lottery numbers split in 3 segment and 5 groups separated by coma and
Space In cell Range from B2:F4,

I enter 5 numbers from draw result in to cells B9:F9

G1 G2 G3 G4 G5 Seg1 4 12, 17 27, 28, 29, 30 32, 37, 38, 40 44, 45, 46, 47, 49, 50 Seg2 2, 6, 7, 8, 9 14, 16, 19 22, 23, 24 36 Seg3 1, 3, 5, 10 11, 13, 15, 18, 20 21, 25, 26 31, 33, 34, 35, 39 41, 42, 43, 48 2 7 14 17 35

Now I want to count 5 draw number how many times have appeared in each and related group.
Result should be shown in cells B13:F15 as in given example, below

G1 G2 G3 G4 G5 Seg1 0 1 0 0 0 Seg2 2 1 0 0 0 Seg3 0 0 0 1 0

Thank to all Mr. Excel team,

I have a worksheet with several fields/cells of which one contains part numbers separated by commas (such as: 8084430-001,8084430-003,8084430-011,8084430-013). These part numbers are of different lengths and count.
I need to place each part number in a cell by itself so that it can be found by a vlookup from another worksheet. Is there a formula or VBA code that can accomplish this? It would take hours to do this manually.

I am trying to create formula which will remove text and some numbers and leave only the code numbers numbers separated by commas.

An example of the starting data in the cell A1 is:

033-Red6, 035-Blue, 043-Grey14, 046-Rain2, 050-Sun1, 052-Deep, 055-East, 056-West22, 064-Tall

Desired result of cell B1:

033, 035, 043, 046, 050, 052, 055, 056, 064

The data is consistent in terms of a 3 digit number followed by a hyphen then a alpha numeric description then comma and repeated up to several hundred.

I think this should be attainable with an array formula but have not been able to make anything work.

Any ideas?

Hello ,

Can someone help me with the problem.

I have entered the below numbers which are separated by a comma in one of the cell:


But as soon as I press enter, it is combining the entire value and changing to a different format but not staying in the above format which I am expected. Please help with a simple solution

Good afternoon people. Thank you in advance. These forums are awesome and I appreciate it.

Have 1 sheet with 2 columns (columns D and F) have numbers in them, if more than 1 number, then commas are used to separate numbers, so 1 comma per 2 numbers, 2 commas per 3 numbers and so on.

In a separate column, (column I), need formula that counts the total amount of numbers and then multiplies the sum by 1200 for each row going down.


Row 7 has column D with (139, 123, 343) and column F with (134), in column I, essentially would be (4 numbers times 1200 equals 4800)

In other words each number, regardless of amounts in column D and F equal 1200 each equally.

Thank you again in advance.

- Nary

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!


I'm using the above formula to join text from columns Z through AC, separated by a comma. I now want to remove the comma at the end of the new string. Also, I would prefer it if the four cells were separated by a slash rather than a comma, but when I simply replace the commas in the formula with slashes I get an error.

Can anyone help?

Many thanks,

I am working with a long column of comma separated data which relates to a column of model id numbers.

The model id number represents a product and the comma separates cell represents the model id numbers for related products.

I need to take the comma separated model id numbers , from a single cell, and blow them out into their own cells.

The goal is to create a one to one relationship between model id number and associated related products.

the date looks like this:

4 35, 36, 45
5 45, 52, 74
6 50, 60, 15
7 78. 33. 11

I need it to split out to:

4 35
4 36
4 45
5 45
5 52
5 74


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)



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!

Can I mix IF with MID command?

I am trying a formula which is not working the way it should. Can you please help me ?

Q16 = 1, 0, 3, 7, 2 (I will have numbers between 1-9 in random order) I want to count all numbers above 1 (I dont want SUM). In this case I want result to be 4. (By COUNTing 1, 3, 7, and 2)

I tried : =IF(MID(Q16,1,1)>1,1)+IF(MID(Q16,4,1)>1,1)+IF(MID(Q16,7,1)>1,1)+IF(MID(Q16,10,1)>1,1)+IF(MID(Q16,13,1)>1,1,0)

I am getting result 5. Wherein it is even counting for a Zero. I want to count only 1-9.

Can you help me

Thanks in advance.


Ok i need a vba script.

Assume in column 1 I have cells running down each with a comma separated list. Ie A1=33,44,55 A2 44,66,77
On another sheet I have a lookup table that matches the numbers to names

such as 33 may be linked to Billy and 22 may link to mary. By link I mean they are in the same row and there is a one to one match

On the original sheet I need to create a new column that with a comma separated list of the names that correspond to the comma separated list next to it.

Basically if A column has a cell with 33,44,55 then B column in the same row should be Dan, Bill, Mike using the lookup table on the other sheet to find the right names

I need a formula to extract cell content in a given column (words separated by spaces) to destination cell into comma separated form:

Ex: Cell (A1) George Michael into: George, Michael


Cell (A2) Last Minute Opportunities Into: Last, Minute, Opportunities

in case which only one word contained in cell, it should be extracted as is.

Any help with that?