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:
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
