How to Count Number of Characters in Excel (3 Easy Ways)

Let’s learn how to count the number of characters in Excel. The count could be performed on a single cell and even a range of cells. While learning that, let’s also shed some light on counting specific characters too. This tutorial will teach you all that is mentioned above using the LEN function with different combos of the SUBSTITUTE, SUMPRODUCT, and SUM functions, working together to do all the monotonous counting for you.

Counting characters or words will at its most be helpful from a designing point of view; How many characters are going onto a business card, the number of words going on a brochure, how many characters will fit according to the size of the object, the font, the design. Having it all counted and sorted gives good perspective!

As for specific embedded characters, you may be looking for certain products via product code, a text symbol that signifies a characteristic, etc. However important or inane your reason, we will find out how to count characters in Excel nonetheless so let’s get counting!

Count Characters In Excel

We will begin with counting the number of characters in Excel. The example we will use is of inside messages in greeting cards. Depending on the size and design of the greeting card, you will have to see which message can fit where. Hence, it will be helpful to see how many characters a message contains. Below is the example sheet:

We will go through on how to count characters in a cell and in a range with the LEN function, which you will be seeing a lot of today.

Count Characters in a Cell

Counting characters in excel can be easily done by using the LEN function. The LEN function returns the number of characters in a text string. Let’s see how that pans out:

Here is the formula we will use to count the number of characters in a cell:

And here is what the applied formula looks like:

The LEN function has been used with a single argument; the reference to the cell for counting the characters in the cell’s text. LEN returns 76 as the number of characters in C4. This count includes space characters. If you want your count to exclude space characters, we’ve got you covered. Read the next segment.

Count Characters in a Cell without Space Characters

If you want the count of the number of characters without space characters, we can slip the SUBSTITUTE function in the LEN function. The SUBSTITUTE function replaces existing text with new text in a text string. The space characters will be swapped for empty text so that the LEN function won’t count the spaces. The formula will look like this:

The SUBSTITUTE function replaces the space characters (" ") in C4 with empty text (""). This way, the text that will be left for the LEN function to count will be Maythisbirthdaybejustthebeginningofayearfilledwithhappymemories. The LEN function returns the counted characters as the outcome.

Count Characters in a Range

If we are to count the number of characters in a range, we may follow something along the lines of adding the individual totals up like:

But while we’re at it, let’s further explore the potential of the LEN function, shall we? We will now show you how to count the number of characters in a range using the LEN and SUMPRODUCT functions and also the LEN and SUM functions together.

LEN with SUMPRODUCT

Here we will combine the LEN and SUMPRODUCT functions to get a total count of characters in a range. The SUMPRODUCT function calculates the products of ranges and then the sum of the products. Further, we are using the SUMPRODUCT function for summing the LEN function’s count as LEN does not calculate arrays on its own. We have 2 formulas below; one counts characters in the range including space characters, the other excludes space characters:

With spaces:

The SUMPRODUCT function is used as a helping hand for the LEN function to operate as an array formula. The job of the LEN function is to calculate the total number of characters in the cells C4:C6 which we sum up using the SUMPRODUCT function.

Without spaces:

Before the LEN and SUMPRODUCT functions get to do their job, the SUBSTITUTE function is used to replace the space characters from C4:C6 with empty characters. The text strings in C4:C6 will then be considered to be without any space characters.

Then the LEN function continues to count the characters and the SUMPRODUCT function sums the values to return 203 as the result.

LEN with SUM

Instinctively the first function that comes to mind for summations is the SUM function and it works just fine with the LEN function too. In fact, it works just like the SUMPRODUCT function above with a small difference. SUMPRODUCT makes the formula work as an array formula but with LEN and SUM, you need to enclose the formula in curly braces to force the formula into an array formula. Let’s see how to do that using the formula below:

With spaces:

Once the formula is added, press the Ctrl + Shift + Enter keys and you will notice curly braces bracketing the formula. This turns it into an array formula so that the LEN function can count the characters in C4:C6 as an array with the SUM function.

Without spaces:

To exclude space characters from the count, we have nested the SUBSTITUTE function to replace all the space characters with empty text. This will leave only letters, numbers, and symbols to be counted. The LEN function counts the characters of each of the cells in C4:C6, the results added by the SUM function. The formula is topped off in the end by hitting the Ctrl + Shift + Enter keys to operate as an array formula and here are the results:

If you’re wondering what happens if the formula isn’t changed into an array formula, you can try and see the result without adding the curly braces. The formula will only return the count of a single cell; the cell that the result is adjacent to, despite the range present in the formula. The curly braces turn it into an array formula so that it can accept the supplied range.

Recommended Reading: Count Non-Blanks cells from a Range

Count Occurrences of a Specific Character

Sometimes you may need to count out a specific character to differentiate some items from others in the list. You could be looking for certain text, numeric code, or a symbol. We will guide you through counting occurrences of a specific character in a cell and in a range and these are methods you can turn towards when sorting and filtering fail you.

Count Occurrences of a Specific Character in a Cell

As an example, we are looking for products in a dataset that are marked down by an asterisk (*). The marked products will show 1 in the results and the unmarked will show 0. We will use the LEN function for the counting and the SUBSTITUTE function for replacing the text. Let’s put it into Excel action. The formula will be:

Using the SUBSTITUTE function, we are replacing the asterisk in C3 with empty text and counting the remnant characters with the LEN function. That brings 7 characters as the count.

It sounds a little questionable up until this point but then we subtract this count from the total character count of C3 which is 8 characters. 8-7=1 which is the result, confirming that C3 does contain an asterisk.

All the codes in our example are 7 characters and a cell not containing an asterisk will compute as 7-7=0.

All the products resulting in 1 from the formula are the marked products and 0s are the unmarked products.

Count Occurrences of a Specific Character in a Range

Utilizing a formula similar to the one used for counting a specific character in a cell, we will put the SUMPRODUCT function and a range into the works to arrive at the count of a specific character in a range. This means we will be using the LEN and SUBSTITUTE functions again along with the SUMPRODUCT function to bring everything together. The formula put together is:

We can use the above formula in our example to find out how many products have been marked with an asterisk. In the formula, all the asterisks in the range C3:C18 have been substituted with empty text by the SUBSTITUTE function. The LEN function counts all the characters in C3:C18 (excluding the asterisks) which would amount to 16 products multiplied by 7-character code, which equals 112 characters.

In the LEN(C3:C18)-LEN(SUBSTITUTE(C3:C18,"*","")) part of the function, the count of 112 is subtracted from the total character count of C3:C18. If we do a quick check of the total count in the sheet, it amounts to 122 characters. 122-112=10 is the outcome of the formula.

But where does the SUMPRODUCT function come in all of this? SUMPRODUCT is the function working to collate the results of the range in the formula and also works to make the LEN function operate as an array formula.

Therefore, we have 10 marked products in our dataset.

Counting down to a characteristic close. Today the LEN function has been quite the friend we can count on (count with, actually). Try using some of these methods next time when faced with something as meticulous as character counting; no one should have to do that alone. And making sure you’re never so forlorn in your Excel adventures, we’ll be back with another facet to explore.