5 Ways Excel Cell Word Count

Introduction to Excel Cell Word Count

When working with Excel, it’s often necessary to count the number of words in a cell. This can be useful for a variety of tasks, such as data analysis, text processing, and report generation. In this article, we’ll explore five different ways to count the number of words in an Excel cell.

Method 1: Using the LEN and SUBSTITUTE Functions

One way to count the number of words in an Excel cell is to use the LEN and SUBSTITUTE functions. The LEN function returns the length of a text string, while the SUBSTITUTE function replaces a specified character with another character. By using these functions together, you can count the number of words in a cell. Here’s an example:

• Enter the following formula in a new cell: =LEN(A1)-LEN(SUBSTITUTE(A1,” “,”“))+1
• Replace A1 with the cell that contains the text you want to count
• Press Enter to calculate the formula

This formula works by subtracting the length of the text string without spaces from the length of the original text string, and then adding 1 to account for the first word.

Method 2: Using the FILTERXML Function

Another way to count the number of words in an Excel cell is to use the FILTERXML function. This function is available in Excel 2019 and later versions, and it allows you to parse and filter XML data. Here’s an example:

• Enter the following formula in a new cell: =FILTERXML(””&SUBSTITUTE(A1,” “,””)&””,“//w”)
• Replace A1 with the cell that contains the text you want to count
• Press Enter to calculate the formula

This formula works by using the SUBSTITUTE function to replace each space with a closing and opening XML tag, and then using the FILTERXML function to count the number of XML tags.

Method 3: Using VBA Macro

You can also use a VBA macro to count the number of words in an Excel cell. Here’s an example:

• Open the Visual Basic Editor by pressing Alt+F11 or navigating to Developer > Visual Basic
• In the Visual Basic Editor, click Insert > Module to insert a new module
• Paste the following code into the module: Function CountWords(text As String) As Long: CountWords = UBound(Split(text, “ “)): End Function
• Save the module by clicking File > Save
• Return to the Excel worksheet and enter the following formula in a new cell: =CountWords(A1)
• Replace A1 with the cell that contains the text you want to count
• Press Enter to calculate the formula

This macro works by using the Split function to split the text string into an array of words, and then using the UBound function to count the number of elements in the array.

Method 4: Using the TEXTSPLIT Function

If you have Excel 2021 or later, you can use the TEXTSPLIT function to count the number of words in a cell. Here’s an example:

• Enter the following formula in a new cell: =COUNTA(TEXTSPLIT(A1,” “))
• Replace A1 with the cell that contains the text you want to count
• Press Enter to calculate the formula

This formula works by using the TEXTSPLIT function to split the text string into an array of words, and then using the COUNTA function to count the number of non-blank elements in the array.

Method 5: Using Power Query

You can also use Power Query to count the number of words in an Excel cell. Here’s an example:

• Go to the Data tab and click From Table/Range
• Select the table or range that contains the text you want to count
• Click the Transform tab and select Split Column > By Delimiter
• Select Space as the delimiter
• Click OK to split the column
• Click the Add Column tab and select Custom Column
• Enter the following formula: =List.Count([Split])
• Click OK to add the column

This method works by using Power Query to split the text string into a list of words, and then using the List.Count function to count the number of elements in the list.

📝 Note: The above methods assume that the text is separated by spaces. If the text is separated by other characters, such as tabs or line breaks, you may need to adjust the formulas accordingly.

To summarize, there are several ways to count the number of words in an Excel cell, including using the LEN and SUBSTITUTE functions, the FILTERXML function, VBA macro, the TEXTSPLIT function, and Power Query. Each method has its own advantages and disadvantages, and the choice of method will depend on the specific needs of your project.