5 Ways Excel Word Count

Introduction to Excel Word Count

When working with text data in Microsoft Excel, it’s often necessary to count the number of words in a cell or a range of cells. While Excel doesn’t have a built-in word count function like Microsoft Word, there are several ways to achieve this task. In this article, we’ll explore five methods to count words in Excel, each with its own advantages and limitations.

Method 1: Using the LEN and SUBSTITUTE Functions

One way to count words in Excel is by using a combination of the LEN and SUBSTITUTE functions. The LEN function returns the length of a text string, while the SUBSTITUTE function replaces a specified character with another character. By using these functions together, we can count the number of spaces in a cell and then add 1 to get the total number of words.

The formula to use is: =LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),” “,”“))+1

Where A1 is the cell containing the text. This formula works by first removing any leading or trailing spaces from the cell using the TRIM function. It then substitutes all spaces with nothing, effectively removing them, and calculates the length of the resulting string. The difference between the original length and the length without spaces, plus 1, gives the word count.

Method 2: Using VBA Macro

For a more automated approach, you can use a VBA macro to count words in Excel. This method involves creating a macro that loops through each cell in a range and uses the Microsoft Word object library to count the words.

The steps to create the macro are:

• Open the Visual Basic Editor in Excel by pressing Alt + F11.
• In the Editor, go to Tools > References and check the box next to “Microsoft Word XX.X Object Library” (where XX.X is your version of Word).
• Insert a new module and paste the following code:

    Sub CountWords()
      Dim cell As Range
      For Each cell In Selection
        Dim wordApp As Object
        Set wordApp = CreateObject(“Word.Application”)
        wordApp.Visible = False
        Dim wordDoc As Object
        Set wordDoc = wordApp.Documents.Add
        wordDoc.Range.Text = cell.Value
        cell.Offset(0, 1).Value = wordDoc.Range.Words.Count
        wordApp.Quit
        Set wordDoc = Nothing
        Set wordApp = Nothing
      Next cell
    End Sub
    

• Save the macro and exit the Visual Basic Editor.
• Select the range of cells you want to count words for and run the macro.

Note: This method requires Microsoft Word to be installed on your computer.

Method 3: Using Power Query

Power Query, a powerful data analysis tool in Excel, can also be used to count words. This method involves creating a custom column in Power Query that uses the Text.Length and Text.Split functions to count the words.

The steps to use Power Query are:

• Go to the “Data” tab in Excel and click on “From Other Sources” > “From Microsoft Query” to open Power Query Editor.
• Load your data into Power Query and add a new custom column.
• In the custom column formula, enter: =List.Count(Text.Split([Text Column],” “))+1
• Where [Text Column] is the name of the column containing your text data.
• Load the data back into Excel, and the new column will contain the word count for each row.

Method 4: Using the Text to Columns Feature

Another method to count words in Excel is by using the Text to Columns feature. This method involves splitting the text into separate columns based on spaces and then counting the number of columns.

The steps to use Text to Columns are:

• Select the cell or range of cells containing the text you want to count.
• Go to the “Data” tab and click on “Text to Columns” in the Data Tools group.
• In the Text to Columns wizard, select “Delimited Text” and click Next.
• Choose “Space” as the delimiter and click Finish.
• Each word will now be in a separate column. You can count the number of columns to get the word count.

Method 5: Using Add-ins

There are several add-ins available for Excel that provide a word count function, such as ASAP Utilities and Excel-Addins. These add-ins often provide a simple formula or button that can be used to count words in a cell or range of cells.

Some popular add-ins for word counting are:

• ASAP Utilities: A free add-in that provides a range of tools, including a word count function.
• Excel-Addins: A collection of add-ins that provide various functions, including word counting.

These add-ins can be easily installed and used to count words in Excel.

📝 Note: When using any of these methods, make sure to adjust the formula or settings according to your specific needs, such as ignoring punctuation or counting only specific types of words.

As we’ve explored the different methods for counting words in Excel, it’s clear that each has its own strengths and weaknesses. By choosing the right method for your specific needs, you can efficiently count words in your Excel spreadsheets.

In summary, the key points to take away are the variety of methods available for counting words in Excel, including using formulas, VBA macros, Power Query, the Text to Columns feature, and add-ins. Each method provides a unique solution to the problem of word counting, and by understanding how to use them, you can improve your productivity and accuracy when working with text data in Excel.