Skip to main content

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 the number of words with Kutools for Excel (several clicks)


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.

Easily count total words in a cell or a range of cells in Excel:

The Count Total Words utility of Kutools for Excel can help you quickly count total words in a cell or a range of cells in Excel as the below demo shown.
Download and try it now! (30-day free trail)


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.


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

Before applying Kutools for Excel, please download and install it firstly.

1. Select a cell for returning the result, then click Kutools > Formula Helper > Formula Helper.

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;
  • Click the OK button. See screenshot:

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

  If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.


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!

Best Office Productivity Tools

Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

kte tab 201905


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • 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.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (32)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Thanks a lot for sharing this formula. I pasted it and changed the cell reference to fit my sheet. Thanks a lot.
This comment was minimized by the moderator on the site
OMG THANK YOU FOR THIS GOOD INFO
This comment was minimized by the moderator on the site
If the cell is empty is is incorrectly displaying a count of 1. To correct this I changed the formula to:


=IF(LEN(TRIM(A1)) > 0, LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),",",""))+1, 0)
This comment was minimized by the moderator on the site
You are the best. The formula worked for me effortlessly. Kudos!!!
This comment was minimized by the moderator on the site
is there any function will count if i kept cells in one color??? ( i mean to know if filled with same colour is there any formula to count the number of colour boxes)
This comment was minimized by the moderator on the site
Is a chance that this function will count only unique words. For example if word exist in a range two times will count only one, will not count duplicates
This comment was minimized by the moderator on the site
Hi,
For counting only the unique words in a range, please apply this formula (please replace A1:A9 with the range based on your needs): =SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)). Thank you for your comment.
This comment was minimized by the moderator on the site
Is chance that this formula will count only unique words. My question is if word will exist 2 times in range will count that word only once without counting duplicates?
This comment was minimized by the moderator on the site
I need to clip words from a paragraph like this

"Advised that device is out of warranty and that no repair available so we would recommend that this unit be removed from svc and replaced if they need a device that will provide audible prompts. Sales rep will go to the customer site and advise them of this.
Closing case while wait"
I want to specify a word and in return I want the preceding and succeeding word to come along with the one I specify, like if I specify "svc" output should be "from svc and" .
please Help
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
This is exactly the problem I have... :( Did you found a solution?
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations