--- Bài mới hơn ---
How To Count Words In Excel
How To Count Number Of Words In Excel Cell? (Using Formulas)
2 Cách Khắc Phục Mở File Excel Chậm
Xử Lý Lỗi Word Bị Chậm, Lâu Mở Hoặc Xử Lý File
Khắc Phục Lỗi Bàn Phím Laptop Gõ Bị Phản Hồi Chậm Trong Word Liệu Có Virus Không?
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 pss 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 pss 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 2022/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 pss 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 conp as follows.
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.
--- Bài cũ hơn ---
Quick Way To Count Words In Excel * Productivity Portfolio
Beginning Java Programming With Hello World Example
Lesson: A Closer Look At The “hello World!” Application
Hello Worldintro To Java Programming: An Interdisciplinary Approach And Computer Science: An Interdisciplinary Approach By Sedgewick And Wayne
Cách Chèn Ghi Chú Ở Chân Trang Trong Word