You might need to count the number of words in your text data for many reasons. Perhaps you have comma-separated lists and need to count the number of items in each list. Unfortunately, Excel doesn’t have a built-in method for counting words. But there are a few clever ways to get the result you need. This post will show you how you can count how many words are in your text data in Excel.
Count Words with the LEN and SUBSTITUTE Functions
This first method will use a combination of the LEN and SUBSTITUTE functions to determine the number of space characters in the text string. Each word is separated by a space, so knowing the number of spaces will also get you the number of words by adding 1. The above formula will return the count of words in cell B3. SUBSTITUTE(B3," “,”") will take the text in cell B3 and replace all the space characters with an empty character. This removes all the spaces from the text. The LEN function will return the total number of characters in a text string. You can then use this to get the number of characters in the original text LEN(B3) and the number of characters in the text when spaces are removed LEN(SUBSTITUTE(B3," “,”")). When you take the difference between these, you get the total number of spaces in the text. Then when you add 1, this is the total number of words. ⚠️ Warning: Your text might not always have only one space between each word. If this is the case then the method above won’t get you the word count. You can first use the TRIM function to ensure your text doesn’t have excess spaces. It will also remove any leading or trailing spaces in the text. This will guarantee the word count based on space count is accurate. The above formula will first remove any excess spaces from the text and then return the word count based on the spaces between words.
Count Words with the TEXTSPLIT and COUNTA Functions
Another formula approach to counting the words in your text is to split the text into separate cells first and then count the number of cells this results in. You can do this with a dynamic array function called TEXTSPLIT. This allows you to split text based on any delimiting character. You can use this function to split your text based on the space characters. Then you can use the COUNTA function to count the number of split values which are the individual words. This means you don’t actually need to split the text into the workbook. They are only split during the formula calculation in memory. The above formula will return the word count from the text in cell B3. The TEXTSPLIT(B3," “,,TRUE) part will split the text based on the space character. Here the TRUE argument skips any blank values that are created during the split due to any excess spaces your text might have. This will help you get an accurate word count based on the spaces. Now you can use the COUNTA function to count the split results. This gives the word count.
Count Words with Power Query
Power Query is another possible way to get a word count. This might be ideal if you are already importing your data from external sources or performing other data transformations. This will open the power query editor and you can add a word count here. This opens the Custom Column formula editor. This will create a new column with the word count results. The Text.PositionOf function will create a list of the position of each space character in the text. The List.Count function then counts this list to get the total number of spaces to which you then add 1 to get a word count. Now you can load this back to Excel. Go to the Home tab of the editor and click on the Close and Load command. Then Choose the Table option from the Import Data menu and press the OK button. This loads the data and new word count column to a new table in your chosen location.
Count Words with VBA
There is no Excel function built for counting words, but you can build your own custom function for this purpose by using VBA. Then you’ll be able to get the word count with a simple reference to the custom function. Go to the Developer tab and click on the Visual Basic option found in the Code section of the ribbon. Alternatively, you can press Alt + F11 on your keyboard to open the visual basic editor. Go to the Insert tab of the visual basic editor and select the Module option to add a new module. Then place the above code in the module. The code creates a function named WORDCOUNT which can be used like any other function in your Excel workbook. The WORDCOUNT function takes a range reference and then will add up the word count for all the cells in the range. It does this by looping through each cell and splitting the text into an array based on the space characters. The number of items in the array plus 1 is then the word count for that cell. This code adds this for each cell to create a running total of the word count as it loops through the range. The above formula can now be used in the worksheet to count the number of words in the range B3:B7.
Count Words with Office Scripts
You can also use Office Scripts to get a word count. You’ll be able to select a range and then run the script which returns the count based on the entire range. Go to the Automate tab and select the New Script option. This opens up the Code Editor and you can paste the above code and press the Save script button. The code will loop through each cell in the selected range and split the text based on the space character. It then counts the number of split items and adds this to the running total. Press the Run button and the code will count the words in the selected range and write the result in the code editor log.
Count Words with Microsoft Word
This method will allow you to count the words in any selected range, but it does require using Microsoft Word as well. You can copy the range of values from Excel and then paste them into a new document in Word. You will then be able to use the word-counting feature in Word. This might not be the most elegant solution, but it will get the job done quickly for those one-time use cases. This will show you a popup with various statistics including the word count from the document.
Conclusions
Excel doesn’t come with a word count feature, but you can still get the same result with a few other methods. Formulas combining the LEN and SUBSTITUTE functions or the COUNTA and TEXTSPLIT functions will get you the required count. Alternatively, you can build your own custom function with VBA that you can use anywhere in the workbook. This will help to simplify the input. Other advanced options such as counting with Power Query or Office Scripts are also possible. You can even use another app to do the work by copying and pasting for one-off word counts. Do you have any extra tips to count words? Let me know in the comments!
