Formula for word count in excel

Asked By 0 points N/A Posted on -

Greetings techyv!

I am having a problem on how to create a formula for word count in excel.

I have searched Google but I don't find a formula which is easy to understand.

I am not a computer savvy please provide me the formula for word count in excel which is easy to understand or with explanations.

Looking forward for your response about my problem.


Best Answer by Hodges Sheilds
Best Answer
Best Answer
Answered By 0 points N/A #166438

Formula for word count in excel


If you are dealing with one cell, that is if the string of words are all in the same cell let us say cell B7, and you want to count these words and have the answer placed let us say in cell D7


You click in the cell D7

And type in the formula below:-

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

Or when you are dealing with a range of cells, let us say you have words in different cells that is to say in cell A1,A2, A3, A4,B1, B2, B3, B4 and put the answer in cell F2

Type in cell F2 the formula below:

  =SUM(IF(LEN(TRIM(A1:B4))=0,0,LEN(TRIM(A1:B4))-LEN(SUBSTITUTE(A1:B4," ",""))+1))

This is what the formula does:

If is logical sentence which tells the application (excel), LEN returns the number of characters in a text string, Trim Removes all spaces from text except for single spaces between words, Substitutes new_text for old_text in a text string.

 and +1 is at end of the formula.

I hope this will help you.

Answered By 0 points N/A #166439

Formula for word count in excel



You will count number of characters in one cell by following formulae
Example “=LEN(A1)”
You may use following formula to count word in a cell.
=IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1)
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1
You may following formula to count number of words in range of cells.
=LEN(TRIM(A1&A2&A3&A4&A5))-LEN(SUBSTITUTE(A1&A2&A3&A4&A5," ",""))+5
=LEN(TRIM(A1&A2&A3&A4&A5))-LEN(SUBSTITUTE(A1&A2&A3&A4&A5," ",""))+Rows(A1:A5)

Login/Register to Answer

Related Questions