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.


  • In Cell C5, enter the following formula:

Count Words in Cell Separated by Commas based on formula combining 3 functions

  • 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


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.


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

Count Words in Cell Manually in Excel

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.


  • 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

Count Words in Cell Separated using a UDF formed by VBA

  • Save the code.
  • Go to cell F5.
  • Enter the following formula (which we just created):

Apply UDF based formula to count word in cell

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

Count_Words = p

The output of the UDF.

End Function

Closes the UDF.

Download Practice Workbook

<< Go Back to Count Words | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF