If you are working on a project that requires the use of spreadsheet, there are many times when you would want to know how to count words in Excel. It is fairly easy for someone who is proficient in Excel to be able to sum up numbers, add dates, convert rates and so on. However, everything that has to do with words in xls is a bit more tricky.
If you are reading this article in another language than English, it has been automatically translated by our WordPress Multilingual Plugin.

There are 5 main ways to count words in Excel:

Use Scrybs automated word counter

The first and most simple way is to use Scrybs’ word counter for free! Scrybs, as an online translation service needs to count the number words in documents submitted by our clients. This includes excel spreadsheets. Merely upload your spreadsheet and the word count will display in the bottom right.

Counting words by using embedded excel functions

There are two formulas that would assist you in finding the number of words in your spreadsheet:

A.) If you need to count the words inside a specific cell you can use specific excel functions. For example, if you need to count the number of words in cell A1, enter the below formula into cell B1 and then hit ENTER. If done correctly, it would count the number of words in cell A1.
=IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1)

B.) If you have several cells within which you need a word-count for, the formula would be slightly altered. For example, if you wish to count the number of words in xls within cells A1:C7 you will be copying the below formula into cell D1. Once, done select Shift + Ctrl + Enter and the number of words in your selected cells will be displayed.
=SUM(IF(LEN(TRIM(A1:C7))=0,0,LEN(TRIM(A1:C7))-LEN(SUBSTITUTE(A1:C7," ",""))+1))

Using predefined functions

Another option to count words in Excel is by a predefined function. To do so, please follow the following instructions:

• Click on Developer, then go to Visual Basic and choose a new Microsoft Visual Basic for applications.
• Once the window is opened click on Insert, choose Module and input the following:
  Function intWordCount(rng As Range) As Integer
intWordCount = UBound(Split(rng.Value, " "), 1) + 1
End Function
• Now that you have defined the function, you can use it in an excel spreadsheet.
• For example, if you want to know the number of words you have in cell A1 you will need to enter the following formula into cell B1:
  =intwordcount(A1)
• Hit the Enter key and you will have your word count!

It is important to note, however, that this method can only be used to count the number of words in a cell and not the number of words in a spreadsheet.

Using user defined functions in VBA

You can also count words in Excel by writing up user defined functions with VBA code.
Start by selecting the cell, or the range of cells you’d like to know the word count for. Once selected click on Developer, then go to Visual Basic and choose a new “Microsoft Visual Basic for Applications”. Once a window is opened click on Insert, choose Module and input the following:
Sub CountWords()
Dim WordCount As Long
Dim Rng As Range
Dim S As String
Dim N As Long
For Each Rng In ActiveSheet.UsedRange.Cells
S = Application.WorksheetFunction.Trim(Rng.Text)
N = 0
If S <> vbNullString Then
N = Len(S) - Len(Replace(S, " ", "")) + 1
End If
WordCount = WordCount + N
Next Rng
MsgBox "Words In ActiveSheet Sheet: " & Format(WordCount,"#,##0")
End Sub
Hit the Play button to bring the code to life, and the number of words in the selected portion will be shown in a pop-up box.

By using Kutools

Lastly, you can also use Kutools to count words in Excel:
You can install Kuttols for Excel and use this tool to count words in a cell or in a range of cells, easily with just few clicks.
Start by selecting the cell you want the result to be displayed in, then click on “Kutools”, go to “Formulas”, and click on “Count words in range”.
Inside the formula helper dialog box you’ll need to enter the cell or cells you wish to have the word count for. Then you would click on “OK”. This will result in the word count being displayed in your previously selected cell.