Top 21 # Word Count Trong Excel / 2023 Xem Nhiều Nhất, Mới Nhất 12/2022 # Top Trend |

How To Count Words In Excel / 2023

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.

End Function

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.

Sub Word_Count_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

End If

WordCnt = WordCnt + N

Next rng

End Sub

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.

Sample File


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?

Quick Way To Count Words In Excel * Productivity Portfolio / 2023

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

How To Count The Number Of Words In A Cell Or A Range Cells In Excel? / 2023

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.

Cách Dùng Hàm Count Trong Excel / 2023

Trong những hàm cơ bản trên Excel, hàm COUNT được xem là hàm cơ bản được thường xuyên sử dụng và áp dụng cho trường hợp đếm ô trong một vùng hay trong toàn bộ bảng dữ liệu.

Với những bảng tính dữ liệu trên Excel, để tính số ô hay dữ liệu nào đó thường sẽ sử dụng đến các hàm đếm trong đó có hàm COUNT. Việc đếm theo cách thủ công là giải pháp không khả thi và không được sử dụng khi xử lý bảng Excel, do khối lượng mà bạn xử lý sẽ khá nhiều. Hoặc khi đếm dữ liệu theo cách thủ công còn có thể không chính xác bằng việc chúng ta thực hiện bằng các hàm chuyên dụng.

Cách sử dụng hàm Vlookup trong Excel

Cách sử dụng hàm ConcateNate trên Excel

Cách dùng hàm AVERAGEIF trên Excel

Hướng dẫn dùng hàm COUNT trong Excel

Hàm COUNT trong Excel có cú pháp hàm là =COUNT(Value1, [value2],….).

Trong đó:

Value 1 là tham số bắt buộc phải có trong công thức. Đây là giá trị đầu tiên để tham chiếu hoặc vùng chọn để đếm ô giá trị.

Value 2 là hàm không bắt buộc, chỉ có thể để đếm tối đa 255 tham số, ô tham chiếu hoặc vùng chọn bổ sung mà bạn muốn đếm giá trị ô.

Những lưu ý khi dùng hàm COUNT:

Hàm COUNT sẽ chấp nhận những tham số cần đếm là các số, ngày tháng hay viết dưới dạng văn bản của số.

Các giá trị logic, số dưới dạng văn bản nhập trực tiếp vào danh sách các tham số trong hàm COUNT sẽ được đếm.

Các tham số có thể chứa hoặc tham chiếu đến các kiểu dữ liệu khác nhau, nhưng hàm COUNT sẽ chỉ đếm số mà thôi.

Với những trường hợp ngoại lệ như các giá trị logic, văn bản hoặc các giá trị không thể dưới dạng số thì phải dùng COUNTA.

Nếu muốn đếm một số nào với điều kiện cho trước thì cần dùng hàm COUNTIFS.

Tại ô kết quả bất kỳ, bạn sẽ nhập công thức =COUNT(C2:C6) rồi nhấn Enter để thực hiện hàm.

Kết quả cuối cùng sẽ cho ra 5 là tổng số loại sách đã được bán ra.

Ngoài ra, bạn có thể thực hiện với vùng dữ liệu khác và cũng cho ra kết quả tương tự =COUNT(E2:E6).

Tuy nhiên nếu bạn nhập công thức để đếm số ô tại cột B thì sẽ không ra kết quả, do dữ liệu cột không phải là dữ liệu số.