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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Methods to Count Words in Cell Separated by Comma in Excel
1. Using Combination of LEN, TRIM, and SUBSTITUTE Functions
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.
- Move to Cell C5 and put in the following formula.
- Press the Enter button and drag the Fill Handle icon.
This section removes unnecessary spaces if exist.
Result: Banana, Grape, Apple, Orange
It replaces the comma (,) with nothing.
Result: Banana Grape Apple Orange
This determines the length of the cell after replacing the comma.
It determined the length of Cell B5 after deleting unnecessary spaces.
Subtract the last two values and add 1.
2. Manual Counting of Words Based on Comma
In this section, we will count words manually based on comma(,).
- 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.
- 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.
- Then, press the Enter button and drag the Fill Handle icon.
The following result we get comparing the Range B5:B14 with the packages.
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 Single
Define 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_1
For loop is applied.
If UCase(WorksheetFunction.Trim(cell_1)) = _ UCase(WorksheetFunction.Trim(value_1)) Then p = p + 1
The 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 = p
It is the return of the UDF.
This is the ending of the UDF.
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. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.