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

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 ...
Count the Number of Cells that Start or End with Specific Text in Excel
How to count cells that match text at the start or the end of a string in Excel. If you want a fuller explanation o ...
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 ...
Export an Excel File to a CSV File
How to export an Excel file to a CSV file (comma separated values file).  This allows you to turn any Excel spread ...

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 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 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.


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

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!

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?

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 very new at this, my terminology will not be good.

I have two columns that I'm working with. In one column, I have a macro that lets me select multiple options from a drop down list of 50+ options, and it will return those values in the cell separated by commas.

I want the 2nd column to search each cell for which values have been selected, and based on that, return a separate set of comma separated values.

the logic behind is it the 50+ options are subcategories, and the 2nd column will be master categories. so one cell would look like

ax,ay,az a ax,by,bz a,b ax,by,cz a,b,c

thank you very much

Hi All,

My problem relates to counting! I like to be able to count x cells in a row as one. The vector or row contians positive numbers, negative numbers and zeros. It can look as follows:

0 0 0 -5 0 0 25 0 0 -10 0 10 12 13 0 0 0 -8 6 7 8 0

If I had a solution for this,the result should be 3. The first instance of a positive number is 25 and this should be counted as 1, next instance is 10, 12 and 13 and these number
should be counted as 1. The last instance is 6, 7 and 8 and these number should also be counted as 1.
Thus, an instance is a string of positve numbers,from just one up to x numbers as long as they are separated by zeros or negative numbers or a combination!

I am grateful for all the help I can get!


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!

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!

I have data that has more than one number in a cell - separated by a comma. i.e. 23,2

I want to count how many times 2 occurs either alone in a cell or as part of a cell that contains a 2 separated by a comma.

I've tried
=countif(range, 2)
=countif(range, *2*)

and didn't get anywhere - any assistance?