Tip: Other languages are Google-Translated. You can visit the English version of this link.
or

Register

or
0
0
0
s2smodern

## 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:

Count number of words with formula

Count number of words in a single cell with User Defined Functions

Count number of words in specified range with VBA code

Easily count number of specific character in a cell:

Kutools for Excel's COUTCHAR utility helps you easily count number of a specific character appearing in a text string in a cell.

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.

#### Count the number of words with formula

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.

2. In the winodw, click Insert > Module, then copy and paste below VBA code into the Module. See screenshot:

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.

Office Tab - Tabbed Browsing, Editing, and Managing of Workbooks in Excel:

Office Tab brings the tabbed interface as seen in web browsers such as Google Chrome, Internet Explorer new versions and Firefox to Microsoft Excel. It will be a time-saving tool and irreplaceble in your work. See below demo:

#### 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.

2. In the winodw, click Insert > Module, then copy and paste below VBA code into the Module. See screenshot:

VBA code: Count number of words in selected range.

```Sub CountWords()
Dim xRg As Range
Dim xRgEach As Range
Dim xRgVal As String
Dim xRgNum As Long
Dim xNum As Long
On Error Resume Next
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)
If xRgEach.Value <> "" Then
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```

3. Press the F5 key to run the code. In the popping up Kutools for Excel dialog box, please select the range you will count number of words inside, and then click the OK button. See screenshot:

Then another Kutools for Excel dialog box pops up to show you the total number of words in seleted range. See screenshot:

#### Count the number of words with Kutools for Excel (only clicks)

You can try the Count words in range utility of Kutools for Excel to easily count number of words in a cell or a range with only several clicks.

Kutools for Excel : with more than 120 handy Excel add-ins, .

1. Select a cell for returning the result, then click Kutools > Formula Helper > Count words in range. See screenshot:

2. In the Formula Helper dialog box, specify the cell or range with words you need to count in the Range box, and then click the OK button. See screenshot:

Then you will get the number of words in specified cell or range.

Tip.If you want to have a free trial of this utility, please go to download the software freely first, and then go to apply the operation according above steps.

#### Easily count the number of words with Kutools for Excel

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now!

### Recommended Productivity Tools

#### Office Tab

Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

#### Kutools for Excel

Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

300 New Features for Excel, Make Excel Much Easy and Powerful:

• Merge Cell/Rows/Columns without Losing Data.
• Combine and Consolidate Multiple Sheets and Workbooks.
• Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
• Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,

Say something here...
symbols left.
###### or post as a guest, but your post won't be published automatically.
People in conversation:
• To post as a guest, your comment is unpublished.
· 24 days ago
It is telling me: "The formula you typed contains error. Please make sure you have typed in the arguments according to the remark of the formula!"

Every cell contains only one word. I also double checked, everything is "text".
Is there a solution to this problem?
• To post as a guest, your comment is unpublished.
· 1 months ago
This is superb. Thank you - just what I needed!!

PS Only.... I thought I was pretty clued up with using excel and now it's made me realise I am a mere novice compared to some!! :-D
• To post as a guest, your comment is unpublished.
· 1 months ago
Hi and thank you for this - the first formula is just what I need, but is there a way to automatically apply it to the same cell in each row please: D1, E1, F1 etc?
• To post as a guest, your comment is unpublished.
· 2 months ago
In cell +1.2+0.25+2+0.8+06 this are in cell count of no not total = 5 please let me know how to calculate in Excel cell
• To post as a guest, your comment is unpublished.
· 2 months ago
=IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1,"+",""))+1), basically change " " (which is a space) to your delimiter (+) and make sure column of +1.2+0.25+2+0.8+06 is TEXT format
• To post as a guest, your comment is unpublished.
· 4 months ago
Thank you! The first formula works great for my purposes.
• To post as a guest, your comment is unpublished.
· 3 months ago
You are welcome, Matt. Glad to be of help.