How to count the number of values separated by a delimiter
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. How to count the number of values separated by a delimiter - 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.
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 explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
User defined function category
This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. […]
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 […]
Excel categories
7 Responses to “How to count the number of values separated by a delimiter”
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)...
Function CountWords(A As String, R As Range) As Long CountWords = Evaluate("COUNT(MATCH({""" & Replace(Replace(Replace(Application.Trim(A), ", ", ","), " ,", ","), ",", """,""") & """}," & R.Address & ",0))") End FunctionRick 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.