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,",","") 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,",","")) returns 8.
Step 3 - Count characters in the original text
LEN(B3) 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, ",", "") 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, ",", "")) returns {8; 6; 7; 2; 5; 11}.
Step 3 - Count characters in the original text
LEN(B3:B8) returns {10; 8; 9; 2; 6; 14}.
Step 4 - Subtract original text length with substituted text length
LEN(B3)-LEN(SUBSTITUTE(B3,",",""))+1 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
Count values category
This post demonstrates how to build formulas that counts unique distinct values based on criteria. The image above demonstrates an […]
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 […]
User defined function category
Table of Contents Search for a file in folder and sub folders - User Defined Function Search for a file […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
This article explains how to perform multiple lookups based on values in one cell with a delimiting character using a […]
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.