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.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Sometimes, getting numbers from a program into Excel, using the formatting you want, can be a challenge. This tip ...Discover More
When you insert cells into a worksheet, Excel needs to know which direction it should shift the displaced cells. If you ...Discover More
When editing information in a cell, you may need to know the result of a portion of your formula. The shortcut described ...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.