Excel Word Count: How to Count Words in Excel

How to Count Words in Excel. Excel lacks a built-in tool to easily count the number of words in a document like Word does. However, it is still possible to do so, although the process can be a bit complicated and only works for one cell at a time. But don’t fret, as we will discuss later on how to apply this method to a group of cells. Let’s delve into the process of word counting in Excel.

How to Count Words in Excel

Excel Functions for Counting Characters: LEN(), TRIM(), and SUBSTITUTE()

Before we can use a formula to count characters in Excel, we need to understand and use three specific Excel functions: LEN(), TRIM(), and SUBSTITUTE().

Using the LEN() Function in Excel

To count the number of characters in a cell, we can use the LEN() function. This function takes a cell with text content as its argument and returns the number of characters in that cell.

For example, if we write “The horse under the tree” in a cell and use the LEN() function in another cell to calculate the number of characters in that sentence, we’ll get 25. Here’s an example of how it works:

Enhancing Your Excel Skills: Counting Words in Excel using LEN(), TRIM() and SUBSTITUTE() Functions

How to Count Words in Excel

Excel lacks a built-in feature that enables you to quickly count words in excel the number of words in a document like Word does. But don’t worry, it is still possible to find out with a few steps. In this article, we will guide you through the process of counting words in Excel using the LEN(), TRIM(), and SUBSTITUTE() functions.

Understanding the LEN() Function in Excel

To count words in excel the number of characters in a cell, we will use the LEN() function. By using LEN() with the cell reference, Excel will calculate the number of characters for us. For example, if we write “The horse under the three” in cell B1 and use the formula =LEN(B1) in cell B2, Excel will return the value of 25 in cell B2.

Using the TRIM() Function in Excel

To remove leading or trailing spaces from the text in a cell, we can use the TRIM() function. For instance, if we have a cell containing ” The horse under the three ” with a length of 34 characters, TRIM() will remove the leading and trailing spaces, leaving us with the original 25 characters. To use TRIM(), we simply add the formula =TRIM(B1) in a new cell, such as cell B4.

How to Count Words in Excel

Applying the SUBSTITUTE() Function in Excel

In case there are spelling errors in the text, we can use the SUBSTITUTE() function to replace specific parts of the text with another word. The syntax for SUBSTITUTE() is SUBSTITUTE(text, old_text, new_text, [instance_num]). For example, we can replace the word “three” with “tree” using the formula =SUBSTITUTE(B4, “three”, “tree”).

How to Count Words in Excel

Counting Words in Excel

Now that we understand how to use these functions, we can combine them to count the number of words in a cell. To count words in excel the words, we will subtract the number of spaces from the total number of characters, and then add one to get the final word count. Here’s the complete formula:

=LEN(TRIM(SUBSTITUTE(B1,” “,” “))) – LEN(SUBSTITUTE(B1,” “,””)) + 1

How to Count Words in Excel

Note that we used a double space (” “) in the TRIM() function to replace any instance of a single space in the text. Also, adding 1 to the result of the formula will give us the correct word count, as Excel counts the spaces between the words as word separators.

With these steps, you can easily count the number of words in a single cell. If you want to count the number of words in a range of cells, you can use the SUM() function with the above formula as an array formula.

How to Count Words in Excel

Improving the Explanation on How to Count Words in Excel

Excel lacks a built-in word count tool or formula. However, we can count characters and derive the number of words by counting the spaces in the text. For instance, the sentence “The horse under the tree” has four spaces, indicating that there are five words. Although counting spaces is not straightforward, we can count the number of characters in the sentence and subtract the number of characters when the spaces have been removed. The difference plus one gives us the number of words.

To implement this process, we can use a formula consisting of three parts. First, we remove spaces at the beginning or end of the sentence using the TRIM() function, then we count the number of characters in the resulting string using LEN(). Second, we remove all spaces using the SUBSTITUTE() function and count the number of characters in the modified text. Finally, we subtract the second count from the first and add one to obtain the number of words.

The complete formula is as follows:

LEN(TRIM(text)) – LEN(SUBSTITUTE(text, ” “, “”)) + 1.

If we need to count the number of words frequently, we can create a custom function in Visual Basic for Applications (VBA). We can open the VBA editor with Alt + F11 (FN + Alt + F11 for Mac), go to Insert > Module, and write the function. We can name the function WORDCOUNT, for instance, and use the same formula with the REPLACE() function instead of SUBSTITUTE(). The custom function will allow us to count words efficiently without typing the formula repeatedly.

Function WORDCOUNT(rng As Range)

Count = 0

For Each cl In rng

thisCount = LEN(TRIM(cl.Value)) – LEN(REPLACE(cl.Value, ” “, “”)) + 1

Count = Count + thisCount

Next

WORDCOUNT = Count

End Function Count Words in Excel

After adding this code to the VBA editor, you have created a custom function in Excel to count the total number of words in a group of cells. However, this function only works for a single range of cells and all cells selected must contain text.

To make this function more versatile, you can explore VBA on your own and try to create your own version. Once you have created the function, you can save it as an Excel Add-In file type (*.xlam) and import it into Excel so that it is available in every workbook.

In conclusion, this article has taught you how to count words in a string in Excel using a custom function. By creating your own function, you can save time and avoid having to write each formula every time you need to count the number of words in a string.