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.

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:

Back to top

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.

Back to top

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.

1. Copy above formula.
2. Double press with left mouse button on cell C3.
3. Paste to cell C3
4. Press and hold CTRL + SHIFT keys simultaneously.
5. Press Enter once.
6. 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.

Back to top

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

Back to top

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:

Back to top

4. Count comma-separated values [UDF]

I received an email from one of my seven blog readers (joke).

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.

Back to top

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

Back to top

4.2 Where to put the VBA code?

To build a user-defined function, follow these steps:

1. Press Alt + F11 to open the visual basic editor.
2. Press with left mouse button on "Insert" on the top menu.
3. Press with left mouse button on "Module", see the image above.
4. Copy the VBA code above and paste it to the code module.
5. Return to Excel.

Back to top

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.

Back to top

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