Formula for Counting Characters and Words in an Excel Cell

One of the most useful tasks when working with text in Excel is automatically counting the number of words or specific characters in a cell. There are many different ways to accomplish this task, but we'll explore the simplest one.



Formula for Counting Words in an Excel Cell

How do you count the number of words and spaces in Excel cells? In reality, words in a string are separated by space characters. There is one space between each word, so the number of spaces in a text string is always one less than the number of words. A simple formula to understand is: number of words in a string = number of spaces + 1. In other words, we need a formula to count the number of spaces in a string and add +1 to the result.

To create a formula in Excel to count the number of words, we need to follow these 3 steps:

1. Determine the length of the original string with spaces.
2. Determine the length of the original string without spaces.
3. Subtract the length of the string with spaces from the string without spaces.

The formula above contains 3 functions. One of them is the SUBSTITUTE function, and the other two are identical LEN functions.

First, the LEN function checks the length of the text string as it is in the original form in cell A1.

=LEN(A2)

Next, the SUBSTITUTE function removes all spaces from the original string:

SUBSTITUTE(A2," ","")

Using the SUBSTITUTE function as an argument for the LEN function allows us to automatically determine the length of the string without spaces.

-LEN(SUBSTITUTE(A2," ",""))+1

Note! At this stage, you should add +1 because the last word in the text does not have a corresponding space.

After that, we just need to subtract the length of the original text from the length of the string without spaces, and as a result, we automatically get the number of words:

=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1

The practical application of the formulas described above is most commonly encountered when analyzing large lists of keywords.

Formula for Counting Characters in an Excel Cell

How to count the number of characters in Excel cells? Similarly, we can count the number of any characters in a word or an entire string. For example, let's count the number of the letter "S" in the word Mississippi:

1. Determine the length of the original word "Mississippi" = 9.
2. Determine the length of the same word but without the letter "S" - "Miiippi" = 6.
3. Subtract the word without the letter "S" from the original word: 9-6=3.

As a result, the formula automatically counted the number of the letter "S" in all the words:

=LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),"S",""))

Note. The UPPER function is used in the formula to make all letters uppercase in words, as the SUBSTITUTE function is case-sensitive. And we are looking for the uppercase letter "S" in each word of the city name.

Example of Using Formulas with the LEN Function in Excel

Let's say we have a list of all expense items for the year for personal finance tracking in Excel. The list is nicely sorted in descending order and presented on a chart for data visualization. But there is a problem with the chart data labels – they are too large and spoil the appearance of the presentation:

To improve the appearance of the chart presentation, we use a smart formula that will shorten the length of the string to one or two words, depending on the specified maximum limit of characters in the string:

Now we have the ability to set the length of the string for data labels ourselves. This way, we get a reduction without distorting the meaning. But if the user needs to fully read the label, it's enough to simply hover the mouse cursor over the corresponding bar of the chart:

Download

The formula for managing the length of a text string is implemented using the LEN function. But this is one of thousands of examples that are practically applied in the process of text processing in cells. The LEN function is one of the most popular and frequently used functions for working with text data in Excel. It is very easy to use and has only one argument.