4+ How to Count Word/Text Count in Excel (Complete+Image)

Have you ever heard of the LEN function? Well, the LEN function is generally used to count the number of text characters and the number of numeric characters.

The question is “how to count the number of words in a cell or excel range?”. In Excel, we can still count the number of words even though Excel itself does not provide a special function to count words (count word).

Therefore, we need to learn a technique for how to count word count in Excel. In this tutorial, we’ll learn things like:

1. How to Count the Total Number of Words in a Cell

The formula used to count the number of words in a sentence in a cell in Microsoft Excel is:

=LEN(Kalimat)-LEN(SUBSTITUTE(Kalimat," ",""))+1

The steps for applying the above formula are:

1. Suppose the data is in cell B2:B5.

2. Click on the cell C2 and type the formula:

=LEN(B2)-LEN(SUBSTITUTE(B2," ",""))+1

An example of using the formula is as follows:

In the picture you will see the entered formula is marked with a blue box and the result of the formula execution is marked with a red box.

Information:

LEN(B2) used to count the total number of characters in cell A2. Then, LEN(SUBSTITUTE(B2;” “;””)) used to count the number of characters of the text without counting spaces.

So, what if there are double spaces? If there are double spaces in the sentence to be calculated, the formula above will produce an incorrect value. Therefore, double spaces must be removed first by using the function TRIM. The excel formula will be as follows:

=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2;" ";""))+1

2. How to Count the Total Number of Words in a Range

How to count the total number of words from a range?
We can count the total words in a range by using an array formula. Array formulas are excel functions or formulas (excel formulas) that involve arrays in their data calculations.

Where the array formula is:

=SUM(LEN(RangeKalimat)-LEN(SUBSTITUTE(RangeKalimat," ",""))+1)

Apart from using array formula, we can also use excel formula that is function Sumproduct. The formula for this function is:

=SUMPRODUCT(LEN(RangeKalimat)-LEN(SUBSTITUTE(RangeKalimat," ",""))+1)

The steps for applying the two formulas above are:

1. Suppose the data is in cell B2:B5.

2. Click on the cell C9 and type the formula:

=SUM(LEN(B2,B5)-LEN(SUBSTITUTE(B2:B5," ",""))+1)

Then press CTRL+Shift+Enter to end it.

3. Click on the cell C10 and type the formula:

=SUMPRODUCT(LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5," ",""))+1)

4. The result will look like the image below where the cells pointed to by the red arrow use the array formula while those pointed to by the blue arrow use the function Sumproduct:

Notes :

The {…} sign is not written manually but appears automatically as a sign that the formula is Array formula which is input by pressing CSE (Ctrl+Shift+Enter) simultaneously.

3. How to Count the Number of Certain Words in a Cell

In this section, we will learn to count the total number of all words in a sentence in excel then we can also count the number of words or only certain texts. The excel formulas used are:

=SUM(LEN(AlamatSel)-LEN(SUBSTITUTE(AlamatSel,"TeksDihitung","")))/LEN("TeksDihitung")

The steps for applying the above formula are:

1. Suppose the data is in cell A2.

2. Click on the cell B5 and type the formula:

=SUM(LEN(A2)-LEN(SUBSTITUTE(A2,"excel","")))/LEN(A5)

An example of its implementation is as follows:

Notes:

The substitution function is case sensitive it means uppercase and lowercase letters are distinguished where the word “IT” and “it” considered different.

So, if you want to count the number of words without distinguishing the case, the text in the cell to be counted will be changed to uppercase or lowercase with the function UPPER or LOWER. Now, to make it clearer, we can see an example of implementing the function UPPER and LOWER below this:

In the picture above there are 2 excel formulas that are applied, namely:

=SUM(LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),UPPER(A5),"")))/LEN(A6)

Or

=SUM(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER(A5),"")))/LEN(A5)

4. How to Count the Number of Certain Words in a Range

Array formulas can also be applied to a range where it must end with Ctrl+Shift+Enter.

The implementation steps for this case are:

1. Suppose the data is in cell B2:B5.

2. You can use an array formula for this case by clicking on the target cell and typing the formula:

=SUM(LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"excel","")))/LEN("excel")

Or use function Sumproduct as:

=SUMPRODUCT(LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"excel","")))/LEN("excel")

3. The results of the execution of the formula will look like the image below:

Also, if you want to ignore upper and lower case then add a function LOWER or UPPER in the two formulas above. Look at the picture below:

From the picture above, it can be seen that the red arrow indicates the use of the array formula, namely:

{=SUM(LEN(B2:B5)-LEN (SUBSTITUTE(LOWER(B2:B5), LOWER("Excel"),"")))/LEN("Excel")}

While the blue arrow indicates the use of the function Sumproduct that is:

=SUMPRODUCT(LEN(B2:B5)-LEN( SUBSTITUTE (LOWER(B2:B5), LOWER("Excel"),"")))/LEN("Excel")

That’s the discussion this time about how to count the number of words or text in Excel. Hopefully useful and good luck. Thank you!

Article Resources

• Counting characters in cells – Office Support | https://bit.ly/2Y8QZ9Z
• Excel Formula Counting Words or Text – Excel Class | https://bit.ly/2Zc5UBK

“If you’re doing your best, you won’t have any time to worry about failure.” H. Jackson Brown, Jr.

Share :