Formula for word count in excel

Asked By edwin atestain 0 points N/A Posted on - 05/20/2013

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.

Thanks!

• Status: Open
• Question Views: 1022
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by Hodges Sheilds

Go To Solution

Best Answer

Best Answer

Answered By Hodges Sheilds 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

Steps

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.

About Hodges Sheilds

Questions
2

Answers
6

Best Answers
2

• Vote Up 0 Vote Down

• Posted on - 05/20/2013
• Question Category: MS Excel

Answered By Hill Benda 0 points N/A #166439

Formula for word count in excel

 

Well,

You will count number of characters in one cell by following formulae

  =LEN(text)

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)

OR

=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

OR

=LEN(TRIM(A1&A2&A3&A4&A5))-LEN(SUBSTITUTE(A1&A2&A3&A4&A5," ",""))+Rows(A1:A5)

 

 

About Hill Benda

Questions
4

Answers
11

Best Answers
3

• Vote Up 0 Vote Down

• Posted on - 05/21/2013
• Question Category: MS Excel