Bạn đang xem bài viết How To Get The Word Count In Excel (Using Simple Formulas) được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung 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.
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:
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:
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()) + 1
There is one option which I always wish Excel should have and that’s count number of words from a cell.
If you work in MS Word there is an inbuilt option on the status bar which shows you how many words are there in the sheet.
But when it comes to Excel there is no such option to count words. You can count the number of cells which have text but not actual words in them.
As you know, in Excel, we have functions and you can use them to calculate almost everything. You can create a formula that can count words from a cell.
Today in this post, you will learn how to count words in Excel from a cell, or a range of cells or even from the entire worksheet. And I’ll also show you how to count a specific word from a range of cells.
Four Different ways to Count Words in Excel
Now without any ado, let’s get started.
1. The Formula to Count Words from a Cell
To count words from a cell you need to combine LEN function with SUBSTITUTE function . And the formula will be (Text is in cell A1):=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1
When you refer to a cell using this formula, it will return 7 in the result. And yes, you have a total of 7 words in the cell.
Before getting into this formula just think this way. In a normal sentence if you have eight words then you will definitely have 7 spaces in those words.
That means you will always have one word more than the spaces. The idea is simple: If you want to count the words, count the spaces and add one in it. Now, to understand this formula you need to split it into three parts.
In the first part, you have used LEN function to count the numbers of characters from the cell A1.
At this point, you have an equation like this.
The total number of characters with spaces and the total number of characters without spaces.
And when you subtract both of these numbers gets the number of spaces and in the end, you have to add one in it. It returns 7 in the result which is the total number of words in the cell.
When you use the above formula it will return 1 even if the cell is blank so it’s better to wrap it with IF function to avoid this problem.
This formula will first check the cell and only return word count if there is a value in the cell.
Apart from the above formulas, I have written a small code to create a UDF for this. This code will help you to create a custom function which will simply return the word count.
In short, you don’t need to combine any functions.
Let me tell you how to use it.
First of all, enter this code in VBA editor.
And then come back to your worksheet, and enter “=MyWordCount(” and refer to the cell in which you have value.
And, it will return the word count.
2. Count Words from a Range of Cells
Now let’s come to the next level.
And here you need to count words from a range of cells instead of a single cell.
The good news is you just need to use the same formula (just a simple change) which you have used above.
In the above formula, A1:A11 is the range of cells and when you enter the formula it returns 77 in the result.
here’s how it works
Do you remember that SUMPRODUCT can take arrays? So when you use it, it returns an array where you have a count of words for each cell.
And in the end, it sums those counts and tells you the count of words in the column.
3. Word Count from Entire Worksheet with VBA Code
This code is one of my useful macro codes list which I use in my work and it can help you to count all the words from a worksheet.
Dim WordCnt As Long
Dim rng As Range
Dim S As String
Dim N As Long
N = 0
N = Len(S) – Len(Replace(S, ” “, “”)) + 1
WordCnt = WordCnt + N
4. Count a Specific Word/Text String from a Range
Here you have a different situation.
Let’s say you need to count a specific word from a range of cells or to check the number of times a value appears in a column.
Below you have a range of four cells and from this range, you need to count the count of occurrence of the word “Monday” . For this, the formula is:
And when you enter it, it returns the count of word “Monday”.
It returns the count of the word (word’s frequency) from the range not the count of the cells which have that word.
Monday is there four times in three cells.
…let me explain how it works
To understand this function, again you need to split it into four parts.
In the first part, LEN function returns an array of the count of characters from the cells.
The second part returns an array of the count of character from the cells by removing the word “Monday”.
In the third part, LEN function returns the length of characters of wor word “Monday”.
After that, subtracts part one from part two and then divide it with part three…
…it returns an array with the count of the word “Monday” from each cell.
In the fourth part, SUMPRODUCT returns the sum of this array and give the count of “Monday” from the range.
Whenever you are typing some text in a cell or a range of cells you can these methods to keep a check on the word count.
I wish someday in future Excel will get this option to count words. But, for the time being, you have all these awesome methods.
Which method do you like the most?
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 Function
2. 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 Sub
Then 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.
Excel can analyze data from many sources. But are you using the Data Model to make your life easier? In this post you learn how to create a pivot table using two tables by using the Data Model feature in Excel.
What is a Data Model
Excel’s Data Model allows you to load data (e.g. tables) into Excel’s memory. It is saved in memory where you don’t directly see it. You can then instruct Excel to relate data to each other using a common column. The ‘Model’ part of Data Model refers to how all the tables relate to each other.
Old school Excel Pro’s, use formulas to create a huge table containing all data to analyze. They need this big table so that Pivot Tables can source a single table. Yet by creating relationships, you surpass the need for using VLOOKUP, SUMIF, INDEX-MATCH formulas. In other words, you don’t need to get all columns within a single table. Through relationships, the Data Model can access all the information it needs. Even when it resides in multiple places or tables. After creating the Data Model, Excel has the data available in its memory. And by having it in its memory, you can access data in new ways. For example, you can start using multiple tables within the same pivot table.
A Simple Task
Imagine your boss wants to have insight in the sales but also wants to know the Sex of the sales person. You got below dataset containing one table with the sales per person and another table containing the salespeople and their respective sex. A way to analyze your data is to use a LOOKUP formula and make a big table containing all information. As a next step you can then use a Pivot Table to summarize the data per sex.
Advantages of the Data Model
Checking and updating formulas may get arbitrary when working with many tables. After all, you need to make sure all the formulas are filled down to the right cell. And after adding new columns, your LOOKUP formulas also need to be expanded. The Data Model requires only little work at setup to relate a table. It uses a common column at the setup. Yet columns that you add later, automatically add to the Data Model.
Working with big amounts of data often results in a very slow worksheet due to calculations. The Data Model however handles big amounts of data gracefully without slowing down your computer system.
Excel 2016 has a limit of 1.048.576 rows. However, the amount of rows you can add to the memory of the Data Model is almost unlimited. A 64-bit environment imposes no hard limits on file size. The workbook size is limited only by available memory and system resources.
If your data resides only in your Data Model, you have considerable file size savings.
Add Data to Data Model
You will now learn how to add tables to the Data Model. To start with, make sure your data is within a table. Using Power Query you can easily load tables into the Data Model.
Select From Table / Range
In the home tab of the Power Query editor
Select Only Create Connection
Make sure to tick the box Add this data to the Data Model
This adds the data to the Data Model. Please make sure to do these steps for both tables.
Creating Relationships Between Data
After adding your data to the Data Model, you can relate common columns to each other. To create relationships between tables:
The Power Pivot screen will appear.
Using the Data Model
Now we come to the exciting part. To use the Data Model in a PivotTable perform the following steps:
The ‘Create PivotTable’ pop-up screen will appear. As you have a Data Model in place, you can now select to use it as data source.
In the PivotTable Fields you will now see all the possible Data Sources for your PivotTable. The yellow database icon on the lower right corner of the marked tables, shows that it is part of Excel’s Data Model.
As the two tables have a relationship between each other, you can use fields from both tables within the same pivot! Read previous sentence again. Isn’t that amazing?? Below example uses the Sales and Seller field from the ProductSales table, while the Sex field comes from the other table. And the numbers are still correct!
Using the Data Model you can analyze data from several tables at once. All without using any LOOKUP, SUMIF or INDEX MATCH formula to flatten the source table. Yet the data analyzed could also come from a database, text file or cloud location. The possibilities are endless.
To minimize the usage of LOOKUP formulas even more, an amazing tool to look at is Power Query. There’s several articles to find about it on my website. For example, you can read how to use Power Query for Creating Unique Combinations or for Transforming Stacked Columns.
Categories Excel report this ad Tags Data Model, Pivot Table, Power Pivot
Cập nhật thông tin chi tiết về How To Get The Word Count In Excel (Using Simple Formulas) trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!