Written by Allen Wyatt (last updated November 12, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
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 lWords As Long Dim Raw As String Dim c As Range lWords = 0 For Each c In Selection If Not c.HasFormula Then ' Get text in cell Raw = c.Value ' Get rid of extra spaces before, after, and within text Raw = Application.Trim(Raw) ' Get rid of any manual line breaks ' and don't assume that they represent the start ' of a new word Raw = Replace(Raw, Chr(10), "") ' Now count spaces in remaining text lWords = lWords + Len(Raw) - Len(Replace(Raw, " ", "")) ' Always increment word count if there is something in ' the cell. If, however, there is nothing there, then ' don't add a word. If Len(Raw) > 0 Then lWords = lWords + 1 End If Next c MsgBox "There are " & lWords & " 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.) Note, as well, that the Trim worksheet function is used instead of the VBA Trim function. The reason is because the worksheet version gets rid of extra spaces in the middle of the text, whereas the VBA function gets rid of only leading or trailing spaces. The word count is then displayed in a message box for your edification.
The macro is very 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.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11748) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Counting Words.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
Insert a symbol into a cell, and it should stay there, right? What if the symbol changes to another character, such as a ...
Discover MoreSometimes getting the right thing to show up in a cell can be a bit tricky when working with dates. If you enter a year ...
Discover MoreWant to select all the data in a contiguous section of a worksheet? The shortcut discussed in this tip makes it very easy.
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2022-11-12 11:11:02
J. Woolley
In my previous comment, this part
SUBSTITUTE(A1:A100,CHAR(10)," "&CHAR(10)))
can be simplified as
SUBSTITUTE(A1:A100,CHAR(10)," "))
2022-11-12 11:03:55
J. Woolley
Assuming the text is in cells A1:A100, here's an Excel 365 formula to count words. (Earlier versions of Excel might not include all of these functions.)
=COUNTA(TEXTSPLIT(TEXTJOIN(" ",TRUE,A1:A100),," ",TRUE))
This will only count words separated by spaces; consecutive spaces are treated as one. If a cell contains a line break CHAR(10) without a space, the word count will not be accurate; for that case, here is an alternate formula:
=COUNTA(TEXTSPLIT(TEXTJOIN(" ",TRUE,SUBSTITUTE(A1:A100,CHAR(10)," "&CHAR(10))),," ",TRUE))
If your Excel version includes TEXTJOIN but not TEXTSPLIT, use My Excel Toolbox's SplitText function instead:
=COUNTA(SplitText(TEXTJOIN(" ",TRUE,TRIM(A1:A100))))
=COUNTA(SplitText(TEXTJOIN(" ",TRUE,TRIM(SUBSTITUTE(A1:A100,CHAR(10)," "&CHAR(10))))))
See https://sites.google.com/view/MyExcelToolbox
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments