Word Count in Excel: Simple Steps for Word Analysis

Excel is popular for data analysis and it’s not just for numbers. You can use it to count words in a text document as well. This can be incredibly useful for writers, editors, or anyone needing to keep track of words for various purposes.

Microsoft Excel does not provide a built-in function to count words in Excel. But using a simple formula we can able to do a word counter analysis in Excel.

In this blog post, we’ll explore some simple & straightforward techniques for counting words in Excel.

Step 1: Using LEN Function

One of the most straightforward methods to count words in Excel is by using the LEN function in combination with some simple formulas. Here’s how:

=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1

This formula calculates the total character count in the cell and subtracts the character count after replacing spaces with nothing. Adding 1 gives you the total word count.

Let’s break down how this formula works:

• LEN(TRIM(A2)): This calculates the total number of characters in the cell after removing extra spaces at the beginning and end of the text (achieved by using the TRIM function). This part of the formula gives you the total character count in the cell.

• LEN(SUBSTITUTE(A2,” “,””)): This calculates the total number of characters in the cell after removing all spaces. By subtracting this value from the total character count, you effectively count the total number of spaces in the text.

• +1: Finally, by adding 1 to the result, you account for the first word in the cell, which doesn’t have a preceding space.

Step 2: Making It Easier with Macros

If you frequently count words in Excel and want to streamline the process, you can create a macro. A macro is a script that automates repetitive tasks.

• Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
• Insert a new module.
• Copy and paste the following VBA code into the module:

Function WordCount(rng As Range) As Long

    WordCount = Len(Trim(rng.Value)) - Len(WorksheetFunction.Substitute(rng.Value, " ", "")) + 1

End Function

• Close the VBA editor and return to your Excel workbook.

Now, you can use the WordCount function in any cell. For example, to count the words in cell A2, you’d enter =WordCount(A2) in a neighboring cell (B2). This approach simplifies the process by creating a custom function for word counting in Excel as shown below.

Step 3: Word Count in Word

Microsoft Word provides a built-in Word Count feature. You can use it to count the words in your text and then import the word count into Excel:

1. Open Your Text in Word:
   • Copy and paste your text into Microsoft Word.

2. Word Count:
   • In Word, go to the “Review” tab and click on “Word Count.”
   • The Word Count dialog box will display the number of words in your text.

3. Copy and Paste it into Excel worksheet

Step 4: Online Word Count Tools

For a quick and easy way to count words, there are various online word count tools available. You can paste your text into these tools, and they will provide you with an accurate word count. After obtaining the word count, you can manually enter it into your Excel worksheet.

Conclusion

Counting words in Excel is a valuable skill for various professionals, including writers, editors, and data analysts. These techniques allow you to efficiently determine word counts, making it easier to manage, edit, or analyze text data. Whether you prefer using Excel’s formulas or leveraging external tools, counting words in Excel is a task that can save you time and effort in various writing and data-related endeavors.

While Excel might not be your first thought for word counting, these clever tricks allow you to quickly and efficiently tally words within your Excel worksheets. Whether you’re analyzing text data, conducting research, or just keeping tabs on the word count in a spreadsheet, this method is a valuable addition to your Excel toolbox.