Cập nhật nội dung chi tiết về Excel Formula: Count Total Words In A Cell 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.
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.
How To Count The Number Of Words In A Cell Or A Range Cells In Excel?
How to count the number of words in a cell or a range cells in Excel?
You can easily count the number of words in MS Word, but Excel doesn’t have a built-in tool for counting the number of words in a worksheet. However, you can count the number of words in Excel with following methods:
Here are two formulas for you to count words in a single cell and in a range cells.
Count words in a single cell
Please enter this formula =IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2,” “,””))+1) into the Formula Bar, and then press the Enter key.
Note: In the formula, A2 is the cell you will count number of words inside.
You can see the result as below screenshot shown:
Count words in a range of cells with array formula
If you want to count the words in a range of cells, please enter formula =SUM(IF(LEN(TRIM(A2:A3))=0,0,LEN(TRIM(A2:A3))-LEN(SUBSTITUTE(A2:A3,” “,””))+1)) into the formula bar, and then press the Shift + Ctrl + Enter keys simultaneously to get the result. See screenshot:
Note: A2:A3 is the range with words you will count.
Count the number of words with User Defined Functions
Also, you can count the words in a cell with the User Defined Functions, please do as follows:
1. Press Alt + F11 keys together to open the Microsoft Visual Basic for applications window.
VBA code: Count number of words in a cell.
Function intWordCount(rng As Range) As Integer 'Update by Extendoffice 2018/3/7 intWordCount = UBound(Split(Application.WorksheetFunction.Trim(rng.Value), " "), 1) + 1 End Function2. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications winodw. Select a blank cell in your worksheet, enter formula “=intwordcount(A2)” into the Formula Bar, and then press the Enter key to get the result. See screenshot:
Note: In the formula, A2 is the cell you will count number of words inside.
If you want to count number of words in a certain range, please apply the following method.
Count number of words in specified range with VBA code
The following VBA code can help you quickly count number of words in a specified range.
1. Press Alt + F11 keys together to open the Microsoft Visual Basic for applications window.
VBA code: Count number of words in selected range.
Sub CountWords() Dim xRg As Range Dim xRgEach As Range Dim xAddress As String Dim xRgVal As String Dim xRgNum As Long Dim xNum As Long On Error Resume Next xAddress = ActiveWindow.RangeSelection.Address Set xRg = Application.InputBox("Please select a range:", "Kutools For Excel", xAddress, , , , , 8) If xRg Is Nothing Then Exit Sub Application.ScreenUpdating = False If Application.WorksheetFunction.CountBlank(xRg) = xRg.Count Then MsgBox "Words In Selection Is: 0", vbInformation, "Kutools For Excel" Exit Sub End If For Each xRgEach In xRg xRgVal = xRgEach.Value xRgVal = Application.WorksheetFunction.Trim(xRgVal) xNum = Len(xRgVal) - Len(Replace(xRgVal, " ", "")) + 1 xRgNum = xRgNum + xNum End If Next xRgEach MsgBox "Words In Selection Is: " & Format(xRgNum, "#,##0"), vbOKOnly, "Kutools For Excel" Application.ScreenUpdating = True End SubThen another Kutools for Excel dialog box pops up to show you the total number of words in seleted range. See screenshot:
Before applying Kutools for Excel, please download and install it firstly.
2. In the Formulas Helper dialog box, please configure as follows.
In the Choose a formula box, select Count total words;Tips: You can check the Filter box, enter a key word to quickly filter the formula as you need.
In the Range box, specify the cell or range in which you want to count total words;
Then you will get the number of words in a specified cell or range.
Easily count the number of words with Kutools for Excel
Kutools for Excel includes 300+ powerful features for Microsoft Excel. Free to try with no limitation in 30 days.Download now!
Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails…
More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
How To Get The Word Count In Excel (Using Simple Formulas)
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()) + 1Quick Way To Count Words In Excel * Productivity Portfolio
I recently attended a workshop with an expert’s panel where people provided their best Excel tips. One tip involved filtering cells with a certain number of words. A nice tip, but it left some attendees wondering how to count words in Excel. The program doesn’t have this feature, but you can find the answer by creating a formula to count words. (Includes sample Excel worksheet and formula)
The summit tip was for a specific product, but opportunities exist where you might want to count words in an Excel cell. I do a similar process when I pull a list of site search queries from my web analytics to see people’s interests.
Spaces and Counting Words
The key to counting words in Excel is to identify the spaces between words correctly. You need to remove leading and trailing spaces in the cells, or the count will be inflated. There are a couple of ways to do this. A simple way is to use Excel add-ons such as ASAP Utilities.
Another way is to use the Excel TRIM function. The trim function removes leading and trailing spaces in a cell. This text function also removes extra spaces between words to just one space. In the screen snap below, you can see that the spaces aren’t always obvious. You have to compare the counts in Columns B and C.
LEN returns the number of characters in a string. In my case, the number will reflect the number for each cell. Since a “space” is considered a character, it is counted.
SUBSTITUTE is similar to “search and replace” on a cell, except we can specify how many times the substitution should occur. For example, you could indicate once, all, or a specific number.
For example, the formula =SUBSTITUTE(A1,"example","sample") would replace the word “example” with “sample” for cell A1.
For our purposes, we want to substitute a space ” ” with nothing. Effectively, the function removes all spaces, so the words run together. “Example text” would change to “Exampletext.”
Understanding the Word Count Formula
One nice feature of Excel is that you can nest formulas that include multiple functions. The formula below references LEN, TRIM, and SUBSTITUTE. It also starts with the IF function, which we’ve outlined before.
Let me break this down for you.
We TRIM any extra spaces in cell A2 and determine if the cell is blank by using =IF(LEN(TRIM(A2))=0,0. If A2 is blank, it assigns the word count as 0.
If A2 isn’t blank, we count the characters in the cell using LEN(TRIM(A2)). You might think of this as our starting character count inclusive of spaces.
We use LEN(SUBSTITUTE(A2)," ","") to remove the remaining spaces. We then count the characters in this new string.
We take the LEN count from Step 2 and subtract the LEN count from Step 3. We then add one to the count to adjust for the first word.
In the above example, I placed the formula in Column B. However, you can also add more columns to show various stages. This often helps when learning a nested formula. In the screen snap below, my formula is in Column F.
If you prefer word problems, think of it this way. If the cell is empty, make the word count = 0. Otherwise, remove the extra spaces and count the characters in the cell. Hold that value as “A.” Now, remove all spaces in that cell and count the characters again. Hold that value as “B.” Your word count is (A-B) + 1.
“Sample example text” = LEN count of 19. This is your “A.”
“Sampleexampletext” = LEN count of 17. This is your “B.”
(19-17)+1 = word count of 3.
One Step Further
Downloadable Resource
attach_file Excel Word Count Lesson File
Disclaimer: Images from Amazon Product Advertising API. I may receive an affiliate commission on these products if you buy. Updated: 2021-04-17
Bạn đang đọc nội dung bài viết Excel Formula: Count Total Words In A Cell 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!