How to Count Words Separated by Comma in Excel Cell

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

Consider the dataset below. We will use the combination of the functions above to count the words separated by commas in the Fruit List column.

Steps:

In Cell C5, enter the following formula:

=LEN(TRIM(B5))-LEN(SUBSTITUTE(TRIM(B5),",",""))+1

Press Enter and drag the Fill Handle icon down to copy the formula to the cells below.

Formula Breakdown:

TRIM(B5)

Removes unnecessary spaces if any exist.

Result: Banana, Grape, Apple, Orange

SUBSTITUTE(TRIM(B5),”,”,””))

Replaces the comma (,) with nothing.

Result: Banana Grape Apple Orange

LEN(SUBSTITUTE(TRIM(B5),”,”,””)

Determines the length of the cell after replacing the comma.

Result: 25

LEN(TRIM(B5))

Determines 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

Method 2 – Counting of Words Manually Based on Comma

We can count the words in cells manually based on the number of commas (,) they contain.

Steps:

There are 3 commas in cell B5. There is one word after the last comma. So, if we add 1 to the number of commas, we will have the correct number of words, 4.

Similarly, count the words in cells B6 and B7.

Method 3 – Using a UDF in Excel VBA

We can also create a user-defined function (UDF) with a VBA macro to perform our count. We will count the words based on a given list.

We have added a list of fruits on the left side of the dataset, and added a column Package No for the groups of Fruits.

Steps:

Go to the sheet name section at the bottom of the sheet.
Right-click and select the View Code option from the Context Menu.

In the VBA window that opens, choose Module from the Insert tab.

Enter the following code in the 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 the code.
Go to cell F5.
Enter the following formula (which we just created):

=Count_Words(E5,$B$5:$B$14)

Press Enter and drag the Fill Handle down to fill the rest of the cells.

The result of comparing the Fruit List with the packages is as follows:

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 Single

Defines variables in the VBA code.

word_1 = Split(m, ",")

VBA split function is applied to split the content of cells, with the comma as the delimiter.

For Each value_1 In word_1
Next value_1

A For loop is applied to cycle through the cells.

If UCase(WorksheetFunction.Trim(cell_1)) = _
UCase(WorksheetFunction.Trim(value_1)) Then p = p + 1

The UCase function converts the strings into uppercase letters. The TRIM function removes unnecessary spaces. The IF function compares the value of the two variables.