Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Counting Words.
by Allen Wyatt
(last updated August 13, 2016)
Words are normally associated with a word processor, such as Microsoft Word. However, many people also work with words in their spreadsheet program. (I had a coworker once who used Excel to write memos all the time.) There may be times when you want to count the number of words in a worksheet that you receive from someone. There are native abilities to perform such a task in Word, but not in Excel.
One solution, of course, is to load your workbook into Word, perform the word count there, and then close the file. This is not nearly as flexible, however, as creating a macro to count words within Excel itself. The following macro, CountWords, counts the number of words in any range you select in a worksheet:
Sub CountWords() Dim MyRange As Range Dim CellCount As Long Dim TotalWords As Long Dim NumWords As Integer Dim Raw As String Set MyRange = ActiveSheet.Range(ActiveWindow.Selection.Address) TotalWords = 0 For CellCount = 1 To MyRange.Cells.Count If Not MyRange.Cells(CellCount).HasFormula Then Raw = MyRange.Cells(CellCount).Value Raw = Trim(Raw) If Len(Raw) > 0 Then NumWords = 1 Else NumWords = 0 End If While InStr(Raw, " ") > 0 Raw = Mid(Raw, InStr(Raw, " ")) Raw = Trim(Raw) NumWords = NumWords + 1 Wend TotalWords = TotalWords + NumWords End If Next CellCount MsgBox "There are " & TotalWords & " words in the selection." End Sub
Notice that the macro steps through each cell in the range you select. It then ignores any cell that contains a formula. In all other cells it essentially counts the number of spaces in the cell. (One or more spaces are assumed to separate words.) The word count is then displayed in a message box for your edification.
The macro is pretty quick on relatively small ranges. If you pick a large range (such as the entire worksheet), then the macro can take a great deal of time to finish its work. The point of this is to make sure that you only select the actual range you want to analyze before invoking the macro.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11748) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Counting Words.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
When developing formulas, you may need to reference the very last value in a particular column. This can seem perplexing, ...Discover More
Enter information into a cell, and Excel needs to figure out what type of information it is. Here's how Excel interprets ...Discover More
Limiting what can be entered in a cell can be an important part of developing a worksheet that other people use. Here are ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.