Count comma separated values
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula in cell C3 that counts strings between a given character in a specific cell.
Table of Contents
1. How to count comma-separated values in a cell?
The following formula counts the number of strings in a single cell using a comma as a delimiting character.
Formula in cell C3:
Explaining formula in cell C3
Step 1 - Substitute comma with nothing
The SUBSTITUTE function replaces a specific text string in a value.
SUBSTITUTE(text, old_text, new_text, [instance_num])
SUBSTITUTE(B3,",","")
becomes
SUBSTITUTE("aa,EE , gg",",","")
and returns "aaEE gg".
Step 2 - Count characters in the substituted text
The LEN function counts the number of characters in a string.
LEN(SUBSTITUTE(B3,",",""))
becomes
LEN("aaEE gg")
and returns 8.
Step 3 - Count characters in the original text
LEN(B3)
becomes
LEN("aa,EE , gg")
and returns 10.
Step 4 - Subtract original text length with substituted text length
LEN(B3)-LEN(SUBSTITUTE(B3,",",""))+1
becomes
10-8+1 equals 3.
2. How to count comma-separated values in a cell range?
Array formula in cell C3:
How to enter an array formula
Enter the formula as a regular formula if you use Excel 365, follow these steps if you use an older version.
- Copy above formula.
- Double press with left mouse button on cell C3.
- Paste to cell C3
- Press and hold CTRL + SHIFT keys simultaneously.
- Press Enter once.
- Release all keys.
The formula will now look like this: {=SUM(LEN(B3:B8)-LEN(SUBSTITUTE(B3:B8, ",", ""))+1)}
Don't enter these characters yourself, they appear automatically.
Explaining formula in cell C3
Step 1 - Substitute comma with nothing
The SUBSTITUTE function replaces a specific text string in a value.
SUBSTITUTE(text, old_text, new_text, [instance_num])
SUBSTITUTE(B3:B8, ",", "")
becomes
SUBSTITUTE({"aa | EE | gg";"jj | oo | pp";"uu | ff | bb";"uu";"xC | Oy";"z | OY | RTE | DSW"},",","")
and returns
{"aaEE gg";"jjoopp";"uuff bb";"uu";"xC Oy";"z OY RTEDSW"}.
Step 2 - Count characters in the substituted text
The LEN function counts the number of characters in a string.
LEN(SUBSTITUTE(B3:B8, ",", ""))
becomes
LEN({"aaEE gg";"jjoopp";"uuff bb";"uu";"xC Oy";"z OY RTEDSW"})
and returns {8; 6; 7; 2; 5; 11}.
Step 3 - Count characters in the original text
LEN(B3:B8)
becomes
LEN({"aa,EE , gg";"jj,oo,pp";"uu,ff, bb";"uu";"xC, Oy";"z, OY, RTE,DSW"})
and returns {10; 8; 9; 2; 6; 14}.
Step 4 - Subtract original text length with substituted text length
LEN(B3)-LEN(SUBSTITUTE(B3,",",""))+1
becomes
{10; 8; 9; 2; 6; 14} - {8; 6; 7; 2; 5; 11} + 1
becomes
{2; 2; 2; 0; 1; 3} + 1
and returns {3; 3; 3; 1; 2; 4}.
3. How to count character-separated values in a cell?
The formula in cell C3 uses the characters given in cell E3 to separate and count values in B3. Cell E3 contains " | ", however, you can use whatever characters you want. For example, you can use this formula to separate and count values using a blank as a delimiting character.
Formula in cell C3:
4. Count comma-separated values [UDF]
I received an email from one of my seven blog readers (joke).
In Excel, I have a column, say A, with some cells blank and some cells with text.
In another column, say B, each *cell* contains many words, separated by a comma.
For every cell in B, I need to check to see how many of the words from column A are in the cell and output the total count of matches.
I built a user-defined function for this, if you have a regular formula you think can solve this, please share. This animated picture explains it all.
The formula in cell C2 is a user defined function. You build a UDF just like a macro using the Visual Basic Editor (VBA).
The first argument (B2) in this custom made function is a cell reference to comma-separated values, in a single cell. The second argument ($A$2:$A$20) is a reference to cell range that you want to count, make sure it is a single column cell reference.
4.1 User-defined function VBA code
Function CountWords(a As String, b As Range) Dim Words() As String Dim Value As Variant, cell As Variant Dim c As Single Words = Split(a, ",") For Each Value In Words For Each cell In b If UCase(WorksheetFunction.Trim(cell)) = UCase(WorksheetFunction.Trim(Value)) Then c = c + 1 Next cell Next Value CountWords = c End Function
4.2 Where to put the VBA code?
To build a user-defined function, follow these steps:
- Press Alt + F11 to open the visual basic editor.
- Press with left mouse button on "Insert" on the top menu.
- Press with left mouse button on "Module", see the image above.
- Copy the VBA code above and paste it to the code module.
- Return to Excel.
4.3 Explaining the user-defined function
Function name and arguments
A user defined function procedure always start with "Function" and then a name. This udf has two arguments, a and b. Variable a is a string and b is a range.
Function CountWords(a As String, b As Range)
Declaring variables
Dim Words() As String Dim Value As Variant, cell As Variant Dim c As Single
Words() is a dynamic string array. Value and cell are variants. c is a single data type. Read more about Defining data types.
Split function
Words = Split(a, ",")
The Split function accepts a text string and returns a zero-based, one-dimensional array containing all sub strings. Split allows you also to specify a delimiting character, default is the space character.
For ... Next statement
For Each Value In Words ... Next Value
Repeats a group of statements a specified number of times. In this case as many times as there are values in the array Words.
If function
If UCase(WorksheetFunction.Trim(cell)) = UCase(WorksheetFunction.Trim(Value)) Then c = c + 1
The Ucase function converts a string to uppercase letters. The WorksheetFunction.Trim method removes all spaces from text except for single spaces between words.
The If function compares cell and Value and if they match 1 is added to c.
The udf returns...
CountWords = c
The udf returns the value in c.
End a udf
End Function
A function procedure ends with the "End function" statement.
Recommended reading
- Excel udf: Lookup and return multiple values concatenated into one cell
- Filter unique words from a range in excel (udf)
- Excel udf: Filter unique distinct values (case sensitive)
- Excel udf: Filter unique distinct records (case sensitive)
- User defined function to split words in a cell range into a cell each in excel
- Excel udf: Filter common values between two cell ranges in excel
- Excel udf: Filter values existing only in one out of two ranges
Count text values category
The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]
Count values category
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double press with left mouse […]
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]
The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]
This article demonstrates techniques on how to count cells based on the background color. I will also demonstrate user defined […]
The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
This article demonstrates ways to count contiguous values in a column, in other words, values that repeat and are adjacent. […]
Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]
NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]
Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]
User defined function category
This user defined function creates a unique distinct list of words and how many times they occur in the selected […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]
The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]
This article demonstrates how to convert a range of cells containing strings separated by a delimiter into a range of […]
This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]
This article demonstrates a User Defined Function (UDF) that counts unique distinct cell values based on a given cell color. […]
The SUBSTITUTE and REPLACE functions can only handle one string, the following User-Defined Function (UDF) allows you to substitute multiple […]
AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]
This article explains how to perform multiple lookups based on values in one cell with a delimiting character using a […]
This article describes a User Defined Function that lookups values in multiple cross reference tables based on two conditions. A […]
Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]
This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]
Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]
This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]
In this vba tutorial I am going to show you how to return values from an udf, depending on where […]
This article describes how to find a sum from a range of numbers using a user defined function. Let´s see […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
7 Responses to “Count comma separated values”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
As long as the Values list in Column A is in alphabetical order, this formula appears to work...
=SUMPRODUCT(0+(LOOKUP(TRIM(MID(SUBSTITUTE(","&B2,",",REPT(" ",999)),ROW(INDIRECT("1:"&LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1))*999,999)),A$2:A$20)=TRIM(MID(SUBSTITUTE(","&B2,",",REPT(" ",999)),ROW(INDIRECT("1:"&LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1))*999,999))))
Rick,
Impressive formula! I had to remove a leading blank in cell A8 to make it count correctly. Perhaps wordpress filtered out some html characters from your formula again?
I don´t know why I made the values in col A in alphabetic order, it was not my intention.
That looks really good.
Is there a way to extract the values (rather than count them) and place the results in column C?
For example:
Cell B3 ("jj,oo,pp").
In column C3, it returns "2" as 2 of the values in cell B3 were found in column A
However, I am after which 2 values were found in column A
i.e. I am after a formula that returns "jj,pp") in cell c3 rather than "2"
is this possible?
thanks
It is LONG, but I can reduce the CountWords UDF to a single line of code (no loops needed)...
Rick Rothstein (MVP - Excel),
Your function returns 2 for values in cell B2? See above. I am not sure why?
That is because your word list is not "pure"... the value in cell A8 has a leading space in front of it... remove it and the UDF will return 3 as expected. In passing, I would not expect a look-up list to have either leading or trailing spaces... if they must be allowed, then I do not think I can modify my one-liner UDF to allow for them.
Thanks for explaining, your one-liner UDF is great.