Do you need to count the words inside a cell or range of cells in your Excel workbook?

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.

= LEN ( B3 ) - LEN ( SUBSTITUTE ( B3, " ", "" ) ) + 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.

= LEN ( TRIM ( B3 ) ) - LEN ( SUBSTITUTE ( TRIM ( B3 ), " ", "" ) ) + 1

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.

= COUNTA ( TEXTSPLIT ( B3, " ",, TRUE ) )

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.

1. Select your table of data.
2. Go to the Data tab.
3. Select the From Table/Range option.

This will open the power query editor and you can add a word count here.

4. Go to the Add Column tab in the editor.
5. Select the Custom Column option.

This opens the Custom Column formula editor.

6. Give the new column a name.

List.Count(Text.PositionOf([Text]," ",Occurrence.All))+1

7. Enter the above formula into the Custom column formula input area where [Text] is the column of text to count the word in.
8. Press the OK button.

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.

Function WORDCOUNT(rng As Range)

Dim arrText() As String
Dim tempCount As Long
tempCount = 0

For Each c In rng
    arrText = Split(c.Value, " ")
    tempCount = tempCount + (UBound(arrText) + 1)
Next c
WORDCOUNT = tempCount

End Function

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.

= WORDCOUNT ( B3:B7 )

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.

function main(workbook: ExcelScript.Workbook) {

    //getselected range
    let rng = workbook.getSelectedRange();
    let rows = rng.getRowCount();
    let cols = rng.getColumnCount();

    var wordCount = 0;

    //loop through selected cells
    for (let i = 0; i < rows; i++) {
        for (let j = 0; j < cols; j++) {
            //count words and add to running total
            let currCell = rng.getCell(i, j).getValue().toString().trim();
            wordCount = wordCount + currCell.split(" ").length;
        };
    };
    console.log(wordCount)
};

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.

1. Copy the range from Excel.
2. Open a new document in Microsoft Word and paste the range from Excel.
3. Go to the Review tab in Word.
4. Click on the Word Count command in the Proofing section.

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!