OzGrid

Count Words in a Cell

< Back to Search results

 Category: [Excel]  Demo Available 

Count Words in a Cell

 

Count Words in a Range of Excel Worksheet Cells

Count Words in a Cell or Range of Cell. See also

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: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

See also:

Count Between Date Ranges
Excel Count If With Multiple Criteria
Examples & Usage Of Excel CountIf Function
Examples & Usage Of Excel Counting Functions
Count Lines in a Cell
Count of Each Item in a List
Excel VBA Macro - Count Or Sum By Fill Color
Count Words in a Cell

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions. The best web models want to talk to you right now. See the best selections free webcam porn online girls from all over the world undress in front of Webcams and arrange real porn. A lot of sensual Amateur porn is filmed on a webcam. Pretty beauty loved to masturbate in front of the pupil of the camera, and enjoy fucking with men, knowing that then it will be possible to look.

 

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.


Gallery



stars (0 Reviews)