# 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 unique distinct values that meet multiple criteria

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 […]

Count Conditionally Formatted cells

This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]

How to count word frequency in a cell range [UDF]

This user defined function creates a unique distinct list of words and how many times they occur in the selected […]

Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]

This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]

List files in a folder and subfolders [UDF]

This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]

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

**Contact Oscar**

You can contact me through this contact form

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.