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 Video Tutorials

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.


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.

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 (i.e.
there are four numbers

in column B)

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, 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?

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



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 have a series if numbers I have imported, probably 1000+. They import into a single cell in groups of 5 separated by a space. I need to be able to count the number of times each number appears. Is there any way to take the imported numbers and place each number in it's own cell? I have tried COUNTIF and it will not count individual numbers in a single cell. I can separate them with a comma by find/replace or I can leave them with a space.

Thanks in advance, JJ in SD

******** ******************** ************************************************************************> Microsoft Excel - Book1 ___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout A1 =
A B C D 1 11*33*38*42*46 * * * 2 4*5*12*18*37 * * * 3 13*15*17*29*38 * * * 4 3*5*8*24*39 * * * 5 8*12*23*25*26 * * * 6 13*23*37*38*40 * * * 7 15*24*26*32*37 * * * 8 29*38*1*9*5 * * * 9 14*15*20*35*44 * * * 10 12*26*27*38*43 * * * Sheet2 *
[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box

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

Howdy. I'm trying to force users to type in a list of numbers separated by a comma (eg 5,13,7,8,1). How would I use Data Validation to accomplish this?


I'm not completely sure if I am dreaming too big here, so please let me know if my idea is a bit ludiculous.

I have a spreadsheet that contains two columns (columns "O" and "P") with serial numbers in them. The serial numbers in these two columns are separated by commas.

I wrote some script to calculate how many commas are in these last two columns. I stored this number in a variable named 'commas' as such.

Lastcell = Cells(Rows.Count, "O").End(xlUp).Row
For i = Lastcell To 1 Step -1
If (Cells(i, "O").Value) "" And Rows(i).EntireRow.Hidden = False_ Then
commas = Len(Cells(i, "O").Value) - Len(Replace(Cells(i, "O").Value, ",", ""))

So, I have my 'commas' variable. What I would like to do is perform a "text to rows" sorta deal. I'm trying to give each serial number its own unique row but have it contain the same information as the columns that come before it. The comma would be used as my delimiter in this particular case.

This process needs to be performed for column "O" first then column "P."

So the jest of it, each serial number (in column "O") gets its own row, and the information stored in columns 'A' through 'N' is right there with it. Then this process needs repeated for column 'P' whilst preserving the the information in columns 'A' through 'N' again.

Needless to say, any help would be greatly appreciated. If any clarification is needed please say so.

hello!. Can anyone tell me how could i count a whole range of cells which contain many numbers separated by alt+enter?. Thanks!

Hoping to get some advice on processing a split and trim operation on a cell containing between 2-4 comma-separated values.

The cell contains days separated by commas, for instance along the lines of...

Thursday, Friday
Monday, Wednesday, Thursday
Monday, Wednesday, Thursday, Friday

I need to translate this into a string containing just the first letter of each day, for instance...


I've tried a couple of variations on the VBA split function, but not having any luck. Would appreciate any advice ... thanks!

Hello all,

I have a problem trying to count the number a certain event happens. I need to count how many times variable A is X at the same time as B is Y. The problem lies that Y is a comma separated list of items (for example first names).

I use the following formula:


The items searched are always on the same row.

The problem lies in this ::: $I$3:$I$214=D$267 ::: part, as when I ask it to look for the items present in cell D267, I cannot tell it to skip any unwanted info.

I would normally do that my adding "*"& to the cell number, such as this:


Unfortunately this returns a 0 count. I know that the "*"& works with a normal CountIf, why does it not work here?

Naturally if I leave the formula as it is, it will only count the times the exact contents of cell D267 are found, but this is not what I need.

To recap I have a situation such as:

Column A has values, 1 per cell, but column B has lists of names, more than one per cell, separated by a comma.

Thank you in advance for the help!