The topic of word count in Excel is quite extensive, so we divided it into two posts. In the first part, “How to Count Text in Excel? Formulas, pitfalls, and tricks,” we covered how to count words in Excel one cell or a range of cells. We also talked about pitfalls and tricks. In this post, we cover the following topics:
To count all the words on a worksheet, you need a VBA code. Don’t panic! Everything is easier than it seems. We gathered all the information about word count in Excel’s entire worksheet, structured it, and clearly described all the steps. You do not need programming skills and any additional software tools to use VBA code. Just follow the steps below.
First, you need to enable the Developer tab. To do this, go to the Files Tab > Options > Customize Ribbon and check the Developer box.
Now go to the Developer tab > Visual Basic.
Next, click on Insert > Module.
You will be surprised, but half of the steps are already behind. There are still a few simple steps left. When the window appears, you need to add the following code:
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
WordCount = WordCount + N
MsgBox “There are ” & Format(WordCount, “#,##0″) & ” words in this worksheet”
Once you’ve added the code, сlick on the play button to check if everything is working as it should.
If everything goes as planned, a popup window will appear, and you will see the word count in Excel’s entire worksheet.
Once you are sure that everything works, click ok. After that, we will see the window with our code again. You need to click on the Save button so that you can use your word count macros later.
The next time you need to count words in Excel entire worksheet, you no longer need to type in the code. You need to run it. To do so, go to the Developer tab and click on the Macros. You can also use the hotkeys Alt + F8.
Select the Word count module and run it.
VBA code is an excellent option if word counting in Excel files is not part of your workflow. If you have more complex documents containing many worksheets, cell comments, text boxes, shapes, headers, and footers, and you have many such files, you need a word count tool that can count everything in one fell swoop with fewer complications.
AnyCount is a word count superhero! It supports 70 file formats and can count any files and anytime. You can add hundreds of Excel files at once, and in a few seconds, it will count all the words, characters, lines, or any other custom units you need. To count words in Excel, easy peasy, download Anycount free.