How to Count Characters and Words in Excel Cells with 3 Methods

While it is easy to calculate the number of words of a paragraph or the entire content in a MS Word document, the thing turns to be different with MS Excel. Unlike MS Word, Excel does not have any in-built tool for counting words in a worksheet. Excel character count can be quite cumbersome were it not for devisable methods and tools. Here we look at handy methods that can be used to count the number of words in Excel.

Method 1. How to Count Excel Characters by Copy and Paste

In case you don’t like to learn anything technical in other methods, we recommend a solution as the first option which is as easy as copy and paste. Now that Excel doesn’t provide the counting feature, we can resort to other tools, like MS Word and online counting tools, which are handy and convenient.

For MS Word, you just need to copy the content from Excel to an opened Word document, then select all the words that you need to count, and look at the figure on the bottom left corner. Just make sure to paste as plain text, otherwise the counting will not work because it is regarded as a table.

For online counting tools, there are various options, but it is surely important to choose the one that includes as many functions as possible. For example, CharacterCountOnline is a good free utility for counting characters, words, sentences, paragraphs and whitespace.

Mark this tool and then any newbies will be able to count words of Excel in a breeze.

Note: Copy and paste is always preferred for both text and file transfer, because Cut and paste could cause data loss if the process is interrupted. In that case, do remember to use iReparo Data Recovery to recover lost files from computer back. It also works for all other common data loss situations.

Method 2. How to Count Excel Words with Formula

In Excel, texts are divided in cells, which makes it sometimes difficult to operate across cells. Fortunately, there are a lot of useful formulas for this purpose. Don’t worry, it is not as hard as you think.

There are two ways to go about it when it comes to using formula to count the number of words in a single cell or a range of cells.

1. Count words in a single cell with formula

When you want to count the number of words in single cell this formula applies:
=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2,” “,””))+1)

Note: A2 should be replaced by the cell that you want to count words inside.

To begin, click on a blank cell, enter the formula in the Formula Bar, then press “Enter” key. The figure of the word count will display in the blank cell.

2. Count words in a range of cells with formula

You can determine the number of words in a range of cells by using array formula as follows.
=SUM(IF(LEN(TRIM(A2:A3))=0,0,LEN(TRIM(A2:A3))-LEN(SUBSTITUTE(A2:A3,” “,””))+1))
Click on a blank cell, paste the formula in the Formula bar, then press Shift + Ctrl + Enter to get the result. The range of the cells is represented by A2:A3 and the results will show the number of words in those cells.

How the formulas work

SUBSTITUTE is used to eliminate the spaces found in a cell by replacing them with an empty text string (“”) which allows the LEN to correctly return the length of the string without spaces.

LEN(SUBSTITUTE(A2,” “,””)): Subtracting the string length without spaces from the total string length and then adding 1 to the final word count allows you to get the exact word count since the number of words in a cell is obtained by the number of spaces +1

The TRIM is used to eliminate any extra spaces in the cell which may be invisible in the worksheet. Two or more spaces may be accidentally present because of typing error at the beginning or end of words or text. If not removed, these extra spaces may alter your word count result. For this reason, the TRIM function will get rid of excess spaces except single spaces between words.

Method 3. How to Count Number of Words in Excel with Microsoft VBA

VBA, short for Microsoft Visual Basic for Applications, can be used to count the number of words in Excel. This involves a few steps as follows.

1. Open Microsoft Visual Basic for Applications by pressing Alt + F11 keys simultaneously.
2. Then click Insert > Module, and paste the VBA code in the module window:
   Function intWordCount(rng As Range) As Integer
   intWordCount = UBound(Split(Application.WorksheetFunction.Trim(rng.Value), ” “), 1) + 1
   End Function
3. Exit Visual Basic by pressing the ALT+Q keys
4. Select a blank cell and enter the formula =intwordcount(A2) in the Formula Bar and press Enter to view the results.
   

You can use VBA to create a custom function especially if you have the need to calculate the number of words often. The main benefit of VBA code is that it is easy to use and can be applied as often as possible to count the number of words in a specified range of cells. You will need to have a code that will create your custom function to get the word count and which can be used regularly just like other functions.

There are many more things that VBA can do, but it requires further learning which is too much for merely Excel character count.

Bottom Line

In conclusion, the three methods in this tutorial will help you easily count the exact number of words in a cell or a range of cells. In addition, there are certain add-on tools which can make your work easier and more efficient and require little technical abilities. Nevertheless, the solutions described above are handy enough for you to count the words in Excel, and they are 100% free. Let us know if you have any question in the comment.