EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Ozgrid, Experts in Microsoft Excel Spreadsheets

Count Words in a Cell

| | Information Helpful? Why Not Donate.

 


NEW! More Books..
Add to Google advanced search! Free Help!

Add Excel Answers & Search To Your Google Toolbar Details

Count Words in a Range of Excel Worksheet Cells

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 Add-ins 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 VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

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 & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates