Skip to main content

Quickly count total words / specific words in Excel

While MS Word offers an easy-to-use feature for counting words, Excel, in contrast, does not include a built-in tool specifically designed for counting words within a worksheet. In this comprehensive guide, we'll explore various methods to count the total number of words in a cell or a range of cells, as well as to count specific words.

Count the total number of words in a cell / range of cells

Count the number of specific words in a cell / rage of cells


Count the total number of words in a cell / range of cells

In this section, we'll introduce some swift and efficient methods for counting the total number of words in a single cell or across a range of cells in Excel.

Count the total number of words by using formulas

● Count total words in a single cell

If you want to get the total number of words in a single cell, please apply the following formula:

=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1)

Then, press Enter key to get the result, see screenshot:

 

● Count total words in a range of cells

To count words across a range of cells, you can use the following array formula:

=SUM(IF(LEN(TRIM(A2:A4))=0,0,LEN(TRIM(A2:A4))-LEN(SUBSTITUTE(A2:A4," ",""))+1))

Then, press Ctrl + Shift + Enter keys together to get the total number of words in the specified cell range. See screenshot:


Count the total number of words by using a useful feature

Kutools for Excel offers a simple and effective way to count words in your sheet. This Count Total Words feature lets you quickly find out how many words are in a cell or a range of cells, without complex formulas. It's a great tool for anyone working with lots of text in Excel, making word counting easy and efficient.

After downloading and installing Kutools for Excel, please do with the following steps:

  1. Click a blank cell to output the calculate result, then click Kutools > Formula Helper > Formula Helper.
  2. In the Formulas Helper dialog, click the Count total words in the Choose a formula section.
  3. Then go to the Argument input section, select a cell or a range of cells that you want to count total words.
  4. Finally, click OK button.

Result:

You will get the total number of words in a specified cell or range of cells. See screenshot:

Tips:
  1. To apply this feature, you should download and install it firstly.
  2. The Formulas Helper feature collects 40+ common used formulas,streamlining a wide range of common tasks and calculations in Excel.

Count the total number of words by using User Defined Function

In Excel, you can also create a user defined function to count total words in a single cell or a range of cells. Please do with the following steps:

  1. Hold down the ALT + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
  2. Click Insert > Module, and paste the following code in the Module Window.
    VBA code: Count total words in a cell or a range of cells
    Function CountWords(rng As Range) As Integer
    'Updateby Extendoffice
        Dim cell As Range
        Dim totalWords As Integer
        totalWords = 0
        For Each cell In rng
            If Len(Trim(cell.Value)) > 0 Then
                totalWords = totalWords + UBound(Split(Trim(cell.Value), " "), 1) + 1
            End If
        Next cell
        CountWords = totalWords
    End Function
    
  3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window. Select a blank cell to put the result, and then enter or copy the below formula:
    Count a range of cells: =CountWords(A2:A4)
    Count a single cell: =CountWords(A2)
  4. Then, press Enter key to get the result:

Count the number of specific words in a cell / rage of cells

In this section, we'll show you easy ways to count the number of times a particular word appears in a cell or a range of cells in Excel.

Count the number of specific words by using formulas

● Count the number of specific words in a single cell

To count how many times a specific word appears in a single cell, please apply the following formula:

=(LEN(A2)-LEN(SUBSTITUTE(A2, "Excel","")))/LEN("Excel")
Note: In the above formula, A2 is the cell from which you want to count the occurrences of a specific word, and “Excel” is the word whose number of occurrences you wish to count.

Then, press Enter key to get the result, see screenshot:

TipCase-insensitive to count specific words in a cell

The above formula is case sensitive, which means it differentiates between uppercase and lowercase letters. It will count occurrences of a specific word exactly as it appears in the formula. For example, "Excel" and "excel" would be counted as different words.

If you need to count the occurrences of a given word regardless of case, you should modify the formula to make it case-insensitive.

=(LEN(A2)-LEN(SUBSTITUTE(A2, "Excel","")))/LEN("Excel")

     

    ● Count the number of specific words in a range of cells

    To count occurrences of a specific word across multiple cells, please apply the following array formula:

    =SUM((LEN(A2:A3)-LEN(SUBSTITUTE(A2:A3, "Excel", "")))/LEN("Excel"))

    Then, press Ctrl + Shift + Enter keys simultaneously to calculate the number of specific words in the selected cell range. See screenshot:

    TipCase-insensitive to count specific words in a range of cells

    To count specific words in a range of cells in a case-insensitive manner in Excel, you can modify the formula so that it does not differentiate between uppercase and lowercase letters. (Remember to press Ctrl + Shift + Enter keys simultaneously to get the correct result.)

    =SUM((LEN(A2:A3)-LEN(SUBSTITUTE((UPPER(A2:A3)), UPPER("Excel"), "")))/LEN("Excel"))


      Count the number of specific words by using a smart feature

      Kutools for Excel makes it super easy to count specific words in a single cell or range of cells. Just select the cells, tell Kutools the word you're looking for, and it'll quickly count them for you – no complicated formulas needed! This tool is great for anyone who needs a fast and simple way to work with text data in Excel.

      1. Click a blank cell to output the calculate result, then click Kutools > Formula Helper > Formula Helper.
      2. In the Formulas Helper dialog, click the Count the number of a word in the Choose a formula section.
      3. Then go to the Argument input section, select a cell or a range of cells which you want to count the number of a specific word from the Text box; select the cell containing the word or type the specific word you will count into the Word box;
      4. Finally, click OK button.

      Result:

      You will get the number of the specific words in a single cell or range of cells. See screenshot:

      Tips:
      1. This feature is case sensitive, it counts occurrences of a specific word exactly as it appears.
      2. To apply this feature, please download and install Kutools for Excel first. Kutools for Excel offers over 40+ common-used formulas, streamlining a wide range of common tasks and calculations in Excel.

      Related Articles:

      • Easily count unique and distinct values
      • Normally, in Excel, the unique values are the values that appear only once in the list without any duplications, and distinct values are all the different values (unique values + 1st duplicate occurrences). When working on a large dataset, you may need to count the number of unique and distinct values among duplicates from a list of cells as below screenshot shown. This tutorial will introduce some quick tricks for counting the unique and distinct values in Excel.
      • Count the number of characters, letters and numbers
      • When you type a list of data in a cell in Excel as shown as below screenshot, you want to count the total number of all characters, or only the number of the letters, or only the numbers in the cell. Now, I talk about the methods on this count in Excel.
      • Count/sum cells by color (background, font, conditional formatting)
      • In daily tasks, color marking is a popular method for quickly distinguishing and highlighting crucial data. But, how do we count or sum cell data based on specific color (fill color, font color, conditional formatting)? By default, Excel doesn't offer a direct feature to count or sum by color. Nevertheless, with some tricks and indirect methods, we can still achieve this. This article will explore how to count or sum data by color.
      • Count Non-blank Cells in Excel
      • This tutorial shows five methods of counting non-blank cells in excel. All the methods are super easy to follow and take less than 10 seconds to get the result.