Excel has lots of functions to perform different tasks. But sometimes one single function is not capable of solving a problem. That time we need to combine two or more functions in a formula. Usually, we use the COUNT function in excel for counting something. But in this article, the situation is not so easy. We need to count words in a cell separated by a comma in Excel. We will discuss the solution to this problem in detail in the below section.
How to Count Words in Cell Separated by Comma in Excel: 3 Methods
1. Using Combination of LEN, TRIM, and SUBSTITUTE Functions
The TRIM function removes all spaces from a text string except for single spaces between words.
The LEN function returns the number of characters in a text string.
The SUBSTITUTE function replaces existing text with new text in a text string.
In this section, we will use the combination of the LEN, TRIM, and SUBSTITUTE functions to count words in a cell separated by commas in Excel.
We will consider the dataset for this operation.
📌 Steps:
- Move to Cell C5 and put in the following formula.
=LEN(TRIM(B5))-LEN(SUBSTITUTE(TRIM(B5),",",""))+1
- Press the Enter button and drag the Fill Handle icon.
Formula Breakdown:
- TRIM(B5)
This section removes unnecessary spaces if exist.
Result: Banana, Grape, Apple, Orange
- SUBSTITUTE(TRIM(B5),”,”,””))
It replaces the comma (,) with nothing.
Result: Banana Grape Apple Orange
- LEN(SUBSTITUTE(TRIM(B5),”,”,””)
This determines the length of the cell after replacing the comma.
Result: 25
- LEN(TRIM(B5))
It determined the length of Cell B5 after deleting unnecessary spaces.
Result: 28
- LEN(TRIM(B5))-LEN(SUBSTITUTE(TRIM(B5),”,”,””))+1
Subtract the last two values and add 1.
Result: 4
2. Manual Counting of Words Based on Comma
In this section, we will count words manually based on comma(,).
📌 Steps:
- We can see 3 commas on cell B5.
- There is one word after the last comma. So, we need to add 1 with the number of commas, and it will be 4.
Similarly, we count the rest of cells B6 and B7.
3. Create a UDF in Excel VBA to Count Words in Cell Separated by Commas
In this section, we will create a user-defined function using the VBA macro to count words. Those words are separated in a cell by a comma. Another thing that needs to mention is that we will count the words based on a given list. Have a look at the section for details.
We have a list of fruits on the left side of the dataset. Also, there are three packages of fruits.
📌 Steps:
- Now, go to the sheet name section at the bottom of the sheet.
- Press the right button of the mouse.
- Choose the View Code option from the Context Menu.
- We enter the VBA window and choose the Module option from the Insert tab.
- After that, put the following VBA code in the VBA module.
Function Count_Words(m As String, b As Range)
Dim word_1() As String
Dim value_1, cell_1 As Variant
Dim p As Single
word_1 = Split(m, ",")
For Each value_1 In word_1
For Each cell_1 In b
If UCase(WorksheetFunction.Trim(cell_1)) = _
UCase(WorksheetFunction.Trim(value_1)) Then p = p + 1
Next cell_1
Next value_1
Count_Words = p
End Function
Save this VBA code.
- Now, move to cell F5.
- Put the following formula created using the VBA code.
=Count_Words(E5,$B$5:$B$14)
- Then, press the Enter button and drag the Fill Handle icon.
The following result we get comparing the range B5:B14 with the packages.
Code Breakdown:
Function Count_Words(m As String, b As Range)Any user-defined function starts with the Function command. This UDF contains 2 arguments.
Dim word_1() As String
Dim value_1, cell_1 As Variant
Dim p As SingleDefine variables in the VBA code.
word_1 = Split(m, ",")Here, VBA split function is applied. This specifies the comma as the delimiter.
For Each value_1 In word_1
Next value_1For loop is applied.
If UCase(WorksheetFunction.Trim(cell_1)) = _
UCase(WorksheetFunction.Trim(value_1)) Then p = p + 1The UCase function is applied here. The UCase function the strings into uppercase letters. Also, the TRIM function removes unnecessary spaces. The IF function compares the value of two variables.
Count_Words = pIt is the return of the UDF.
End FunctionThis is the ending of the UDF.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, we described 3 steps to count words in a cell separated by a comma in excel with proper explanations. We also add a VBA code to create a user-defined function.
<< Go Back to Count Words | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
