# 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

The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]

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

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]

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

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 post describes how to split words in a cell range into a cell each using a custom function. I […]

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

This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]

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

Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]

This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]

This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]

This article describes how to find a sum from a range of numbers using a user defined function. Let´s see […]

In this vba tutorial I am going to show you how to return values from an udf, depending on where […]

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