How to Count Words Separated by Comma in Excel Cell

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

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

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

  • 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

Read More: How to Count Words in Excel with Formula (2 Handy Examples)


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.

Count Words in Cell Manually in Excel

Similarly, we count the rest of Cells B6 and B7.

Read More: How to Count Words in Excel Column (5 Useful Ways)


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

Count Words in Cell Separated using a UDF formed by VBA

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)

Apply UDF based formula to count word in cell

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

End Function

This is the ending of the UDF.

Read More: Excel Formula to Count Specific Words in a Cell (3 Examples)


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. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo