Do a Word Count in Excel

Do a Word Count in Excel

Posted 2014-04-22 by Mihaela Schwartzfollow
Counting the words of an Excel file can be a frustrating task if you don't have the right formula. Translators and writers using Excel files might find this tutorial very helpful.

A common approach you have probably already tried is using the "Save As" function and saving the worksheet as text (doc or docx, depending on the version of Office you are using). The second step is opening it with Word and counting the words as if it was a regular document.

This approach might seem easy, but it presents a series of drawbacks. For instance, when saving an Excel file as a document, you might lose parts of the text and obtain as result word counts that are lower than what you have actually worked on.

an Excel worksheet
some place holder text

  • Create an Excel file and fill in some of the columns with place holder text. You have use the Latin "lorem ipsum" or you can simply copy paste a text you have already written in a different file.
  • Alternatively, you can also use an existing Excel file to count the words, but it is advisable you save a copy until you get the ropes of it.

  • If your text is in column A, add the following formula in column B:
  • =IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ","")) 1)
    The role of the TRIM function is to remove any extra spaces in the text so that you obtain an accurate word count.
  • The formula you placed in B1 cell will count the words in A1. If your Excel file contains more than one text cell, copy the formula for each cell: for A2, place the formula in B2 and so on. You can do that by copy/pasting or by dragging the right corner of the cell containing the formula.

  • If you want to get the total word count, use a SUM function. Insert formula by clicking on the fx (function field) and decide the value of each cells to be added:

    You will obtain the correct word count for all your cells without too much effort:

    If you work frequently with Excel files and need a word count for them, if would be a good idea to create a template for yourself so that you don't do the whole process each time.

    229771 - 2023-07-17 11:44:38


    Copyright 2024 OatLabs ABN 18113479226