Cập nhật nội dung chi tiết về How To Get The Word Count In Excel (Using Simple Formulas) mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.
Want to get the word count in Excel? Believe it or not, Excel does not have an inbuilt word counter.
But don’t worry.
A cool bunch of excel functions (or a little bit of VBA if you’re feeling fancy) can easily do this for you.
In this tutorial, I will show a couple of ways to count words in Excel using simple formulas. And at the end, will also cover a technique to create a custom formula using VBA that will quickly give you the word count of any text in any cell.
Formula to Get Word Count in Excel
Before I give you the exact formula, let’s quickly cover the logic to get the word count.
Suppose I have a sentence as shown below for which I want to get the word count.
While Excel cannot count the number of words, it can count the number of spaces in a sentence.
So to get the word count, we can count these spaces instead of words and add 1 to the total (as the number of space would be one less the number of words).
Now there can be two possibilities:
There is a single space between each word
There are multiple spaces between words.
So let’s see how to count the total number of words in each case.
Example 1 – When there is a single space between words
Let’s say I have the following text in cell A1: Let the cat out of the bag
To count the number of words, here is the formula I would use:
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1
This would return ‘7’ as a result.
Here is how this formula works:
LEN(A1) – This part of the formula returns 26, which is the total number of characters in the text in cell A1. It includes the text characters as well as the space characters.
SUBSTITUTE(A1,” “,””) – This part of the formula removes all the spaces from the text. So the result, in this case, would be Letthecatoutofthebag.
LEN(SUBSTITUTE(A1,” “,“”) – This part of the formula counts the total number of characters in the text that has no spaces. So the result of this would be 20.
LEN(A1)-LEN(SUBSTITUTE(A1,” “,“”)) – This would subtract the text length without spaces from the text length with spaces. In the above example, it would be 26-20 which is 6.
=LEN(A1)-LEN(SUBSTITUTE(A1,” “,“”))+1 – We add 1 to the overall result as the total number of spaces is one less than the total number of words. For example, there is one space in two words and two spaces in three words.
Now, this works well if you have only one space character between words. But it wouldn’t work if you have more than one space in between words.
In that case, use the formula in the next example.
Example 2: When there are multiple spaces between words
Let’s say you have the following text: Let the cat out of the bag
In this case, there are multiple space characters between words.
To get the word count, we first need to remove all the extra spaces (such that there is only one space character between two words) and then count the total number of spaces.
Here is the formula that will give us the right number of words:
=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1
This is a similar formula used in the above example, with a slight change – we have also used the TRIM function here.
Excel TRIM function removes any leading, trailing, and extra spaces (except single spaces between words).
The rest of the formula works the same (as explained in Example 1).
Note: If there are no spaces between words, it is considered as one word.
Using VBA Custom Function to Count Words in Excel
While the above formulas work great, if you have a need to calculate the word count often, you can use VBA to create a custom function (also called a User Defined Function).
The benefit of using a custom function is that you can create it once and then use it like any other regular Excel function. So instead of creating a long complex formula as we did in the two examples above, you have a simple formula that takes the cell reference and instantly gives you the word count.
Here is the code that will create this custom function to get the word count in Excel.
Function WordCount(CellRef As Range) Dim TextStrng As String Dim Result() As String Result = Split(WorksheetFunction.Trim(CellRef.Text), " ") WordCount = UBound(Result()) + 1Excel Formula: Count Total Words In A Cell
Excel doesn’t have a dedicated function for counting words in a cell. However, with a little ingenuity, you can create such a formula using the SUBSTITUTE and LEN functions, with help from TRIM, as shown in the example. At a high level, this formula uses the LEN function to count the number of characters in the cell, with and without spaces, then uses the difference to figure out the word count. This works, because word count is equal to the number of spaces + 1, so long as there is one space between each word.
The first part of the formula counts the characters in cell B5, after removing extra space:
=
LEN
(
TRIM
(
B5))
// normalize space, count characters
Inside LEN, the TRIM function first removes any extra spaces between words, or at the beginning or end of the text. This is important, since any extra spaces will throw off the word count. In this case, there are no extra space characters, so TRIM returns the original text directly to the LEN function, which returns 30:
LEN
(
"All Quiet on the Western Front"
)
// returns 30
At this point, we have:
=
30
-
LEN
(
SUBSTITUTE
(
B5,
" "
,
""
))
+
1
Next, we use the SUBSTITUTE function to remove all space characters from the text:
SUBSTITUTE
(
B5,
" "
,
""
)
// strip all space
Notice SUBSTITUTE is configured to look for a space character (” “), and replace with an empty string (“”). By default, SUBSTITUTE will replace all spaces. The result is delivered directly to the LEN function, which returns the count:
LEN
(
"AllQuietontheWesternFront"
)
// returns 25
LEN returns 25, the number of characters remaining after all space has been removed. We can now simplify the formula to:
=
30
-
25
+
1
// returns 6
which returns 6 as a final result, the number of words in cell B5.
Dealing with blank cells
The formula in the example will return 1 even if a cell is empty, or contains only space. This happens because we are adding 1 unconditionally, after counting space characters between words. To guard against this problem, you can adapt the formula as shown below:
Notice we've replaced 1 with this expression:
This code first trims B5, then checks the length. If B5 contains text, LEN returns a positive number, and the expression returns TRUE. If B5 is empty, or contains only space, TRIM returns an empty string ("") to LEN. In that case, LEN returns zero (0) and the expression returns FALSE. The trick is that TRUE and FALSE evaluate to 1 and zero, respectively, when involved in any math operation. As a result, the expression only adds 1 when there is text in B5. Otherwise, it adds zero (0). This logic could also be written with the IF function statement like this:
and the result would be the same. The expression above is simply more compact.
Functions And Formulas That You Can Use In A Word Document
You can use simple formulas in Microsoft Word, such as addition (+), subtraction (-), multiplication (*), or division (/). Also, you can calculate a power of (^):
See How to reference a cell of a Word table for more details.
All functions you can see in the Paste function drop-down list of the Formula dialog box:
ABS ()
Calculates the absolute value of the value inside the parentheses.
AVERAGE ()
Calculates the average of the elements identified inside the parentheses.
COUNT ()
Calculates the number of elements identified inside the parentheses.
DEFINED ()
Evaluates whether the argument inside parentheses is defined. Returns 1 if the argument has been defined and evaluates without error, 0 if the argument has not been defined or returns an error.
IF ()
Evaluates the first argument. Returns the second argument if the first argument is true; returns the third argument if the first argument is false.
INT ()
Rounds the value inside the parentheses down to the nearest integer.
MAX ()
Returns the maximum value of the items identified inside the parentheses.
MIN ()
Returns the minimum value of the items identified inside the parentheses.
MOD ()
Takes two arguments (must be numbers or evaluate to numbers). Returns the remainder after the second argument is divided by the first. If the remainder is 0 (zero), returns 0.0.
NOT
Evaluates whether the argument is true. Returns 0 if the argument is true, 1 if the argument is false. Mostly used inside an IF formula.
OR ()
Takes two arguments. If both are false, returns 0, else returns 1. Mostly used inside an IF formula.
PRODUCT ()
Calculates the product of items identified inside the parentheses.
ROUND ()
Rounds the first argument to the number of digits specified by the second argument. If the second argument is greater than zero ( 0), first argument is rounded down to the specified number of digits. If second argument is zero ( 0), first argument is rounded down to the nearest integer. If second argument is negative, first argument is rounded down to the left of the decimal.
SIGN ()
Takes one argument that must either be a number or evaluate to a number. Evaluates whether the item identified inside the parentheses if greater than, equal to, or less than zero ( 0). Returns 1 if greater than zero, 0 if zero, -1 if less than zero.
SUM ()
Calculates the sum of items identified inside the parentheses.
The arguments can be:
See also this tip in French: Fonctions et formules dans Word.
How To Use The Excel Counta Function
Random list of names
At the core, this formula uses the INDEX function to retrieve 10 random names from a named range called “names” which contains 100 names. For example, to retrieve the fifth name from the list, we use INDEX like this…
Add row numbers and skip blanks
In the example shown, the goal is to add row numbers in column B only when there is a value in column C. The formula in B5 is:
=IF(ISBLANK(C5),””,COUNTA($C$5:C5))
The IF function first checks if cell C5 has…
Cell contains all of many things
The key is this snippet:
ISNUMBER(SEARCH(things,B5)
This is based on another formula (explained in detail here) that simply checks a cell for a single substring. If the cell contains the substring, the formula…
Count cells that are blank
The COUNTBLANK function counts the number of cells in the range that don’t contain any value and returns this number as the result. Cells that contain text, numbers, dates, errors, etc. are not counted. COUNTBLANK is…
Last row in mixed data with no blanks
This formula uses the COUNTA function to count values in a range. COUNTA counts both numbers and text to so works well with mixed data.
The range B4:B8 contains 5 values, so COUNTA returns 5. The number 5 corresponds…
Count unique values
This example uses the UNIQUE function to extract unique values. When UNIQUE is provided with the range B5:B16, which contains 12 values, it returns the 7 unique values seen in D5:D11. These are returned directly to the…
Dynamic named range with OFFSET
This formula uses the OFFSET function to generate a range that expands and contracts by adjusting height and width based on a count of non-empty cells.
The first argument in OFFSET represents the first cell in the…
Running count group by n size
The core of this formula is the COUNTA function, configured with an expanding range like this:
COUNTA($B$5:B5)
As the formula is copied down the column, the range starting with B5 expands to include each new row, and…
Count cells not equal to many things
First, a little context. Normally, if you have just a couple things you don’t want to count, you can use COUNTIFS like this:
But this doesn…
Project complete percentage
In this example if a task is marked “Done”, then it is considered complete. The goal is to calculate the percent complete for the project by showing the ratio of complete tasks to total tasks, expressed as a percentage…
Count sold and remaining
The COUNTA function counts non-blank cells that contain numbers or text. The first COUNTA counts non-blank cells in the range B5:B11 and returns the number 7:
COUNTA(B5:B11)
The second COUNTA function…
Generate random text strings
The new dynamic array formulas in Excel 365 make it much easier to solve certain tricky problems with formulas.
In this example, the goal is to generate a list of random 6-character codes. The randomness is handled by…
Sort by random
The SORTBY function allows sorting based on one or more “sort by” arrays, as long long as they have dimensions that are compatible with the data being sorted. In this example, there are 10 values being sorted, the…
Score quiz answers with key
This formula uses the named range “key” (C4:G4) for convenience only. Without the named range, you’ll want to use an absolute reference so the formula can be copied.
In cell I7, we have this formula:
=SUM(–(C7:G7=…
Reverse a list or range
The heart of this formula is the INDEX function, which is given the list as the array argument:
=INDEX(list
The second part of the formula is an expression that works out the correct row number as the formula is…
Bạn đang đọc nội dung bài viết How To Get The Word Count In Excel (Using Simple Formulas) trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!