
NEW! More Books.. 
Add Excel Answers & Search To Your Google Toolbar Details 
Count Words in a Cell or Range of Cell. See also
See Also: Summing, Counting & Pivot Tables in Excel
Unlike Microsoft Word, Excel does not give us a ready made way to find out the number of words in a cell, or a range of cells containing text, or words. However, with the help of the SUBSTITUTE function/formula and the LEN function/formula we can. If you are not familiar with these functions/formulas I have written an explanation below.
SUBSTITUTE
Syntax
=substitute(text,old_text,new_text,instance_num)
What it does
Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want
to replace specific text in a text string; use REPLACE when you want to replace
any text that occurs in a specific location in a text string.
Example
=SUBSTITUTE(A1, "Sales", "Cost") If A1 had the text "Sales Data" the formula
result would be "Cost Data".
LEN
Syntax
=len(text)
What it does
LEN returns the number of characters in a text string.
Example
=LEN(A1) If A1 had the text "Sales Data" the formula result would be 10 as A1
has 9 text characters and 1 space character.
Count Words in a Cell
The formula below will return the number of words (not characters) in cell A1
=LEN(A1)LEN(SUBSTITUTE(A1," ",""))+1
Be aware that superfluous spaces are also counted and may give misleading results. To ensure accuracy we can simply nest the TRIM formula function/formula in the first LEN
=LEN(TRIM(A1))LEN(SUBSTITUTE(A1," ",""))+1
Count Words in a Range of Cells
The formula below will return the number of words (not characters) in cells A1:A5
=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)
Keep in kind that the formula above WILL return 1 for an empty cell, due to +1. Overcome that problem by use of this function;
=LEN(TRIM(A1))LEN(SUBSTITUTE(TRIM(A1)," ",""))+(LEN(A1)>1)
The +(LEN(A1)>1) will either return TRUE (1) or FALSE (0).
See Also: Summing, Counting & Pivot Tables in Excel
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Addins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages  Trading In Excel  Construction Estimators  Finance Templates & Addins Bundle  CodeVBA  SmartVBA  PrintVBA  Excel Data Manipulation & Analysis  Convert MS Office Applications To......  Analyzer Excel  Downloader Excel
 MSSQL Migration
Toolkit 
Monte Carlo Addin 
Excel
Costing Templates