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.
      Comments (32)
      No ratings yet. Be the first to rate!
      This comment was minimized by the moderator on the site
      Thanks a lot for sharing this formula. I pasted it and changed the cell reference to fit my sheet. Thanks a lot.
      This comment was minimized by the moderator on the site
      OMG THANK YOU FOR THIS GOOD INFO
      This comment was minimized by the moderator on the site
      If the cell is empty is is incorrectly displaying a count of 1. To correct this I changed the formula to:


      =IF(LEN(TRIM(A1)) > 0, LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),",",""))+1, 0)
      This comment was minimized by the moderator on the site
      You are the best. The formula worked for me effortlessly. Kudos!!!
      This comment was minimized by the moderator on the site
      is there any function will count if i kept cells in one color??? ( i mean to know if filled with same colour is there any formula to count the number of colour boxes)
      This comment was minimized by the moderator on the site
      Is a chance that this function will count only unique words. For example if word exist in a range two times will count only one, will not count duplicates
      This comment was minimized by the moderator on the site
      Hi,
      For counting only the unique words in a range, please apply this formula (please replace A1:A9 with the range based on your needs): =SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)). Thank you for your comment.
      This comment was minimized by the moderator on the site
      Is chance that this formula will count only unique words. My question is if word will exist 2 times in range will count that word only once without counting duplicates?
      This comment was minimized by the moderator on the site
      I need to clip words from a paragraph like this

      "Advised that device is out of warranty and that no repair available so we would recommend that this unit be removed from svc and replaced if they need a device that will provide audible prompts. Sales rep will go to the customer site and advise them of this.
      Closing case while wait"
      I want to specify a word and in return I want the preceding and succeeding word to come along with the one I specify, like if I specify "svc" output should be "from svc and" .
      please Help
      This comment was minimized by the moderator on the site
      It is telling me: "The formula you typed contains error. Please make sure you have typed in the arguments according to the remark of the formula!"


      Every cell contains only one word. I also double checked, everything is "text".
      Is there a solution to this problem?
      This comment was minimized by the moderator on the site
      This is exactly the problem I have... :( Did you found a solution?
      This comment was minimized by the moderator on the site
      This is superb. Thank you - just what I needed!!

      PS Only.... I thought I was pretty clued up with using excel and now it's made me realise I am a mere novice compared to some!! :-D
      This comment was minimized by the moderator on the site
      Hi and thank you for this - the first formula is just what I need, but is there a way to automatically apply it to the same cell in each row please: D1, E1, F1 etc?
      There are no comments posted here yet
      Load More
      Please leave your comments in English
      Posting as Guest
      ×
      Rate this post:
      0   Characters
      Suggested Locations