How to Count Characters in Cells
(Excel Formula)

Have you ever wondered how to count the number of characters in a string in Excel? We bet you have.

In MS Word, you can find the character and word count at the bottom of the screen but things are a little different for Excel 🤔

We usually use the LEN function to find the number of characters in a text string in Excel. If you want to find the number of occurrences of a certain character, etc. then you use a more complex version of the formula in combination with other functions 🤝

We will see each of these in detail in the tutorial below. Download our sample workbook here to practice along the guide. Let’s get right into the article.

Table of Contents

LEN function in Excel

The LEN function is derived from the word length and its name is easy to remember. The purpose of the LEN function is to return the length of data in a cell with or without spaces.

It could be a text string, random characters, special characters, numbers or anything else.

In our case, you can use this function to find the count of characters in a cell. But before that, let’s quickly see the syntax of the LEN function 📏

where,

text refers to the cell reference or string whose length you want to find out.

If you directly write a text string in the formula, you would have to add quotation marks at the start and end as we do in any other formula.

How to count characters

Let’s now see a real-life example of how to count characters using the LEN function.

Single Cell

Say, we have the following sample data, where we want to find the length of string in cell A2 🧐

To find the total number of characters in a single cell,

Step 1) Select cell B2.

Step 2) Begin by writing the following formula.

Step 3) Press Enter.

The function returns the length of the string as 16 which is correct 😀

Multi-Cell

The process of finding the length of strings in multiple cells is the same as the above formula.

Say, we have the following sample data with different cells.

To find the length of each cell,

Step 1) Select cell E2.

Step 2) Type in the formula.

Step 3) Press Enter.

Step 4) Now double-click the Fill Handle (the small square box at the right bottom of the active cell) to copy the formula to the remaining cells.

And voila! It’s done 😃

We get the length of each cell in our list in one go 🎯

How to count total characters in multiple cells

The most obvious way to get the total count of all characters in a range of cells is to add the lengths using the SUM function.

Say, we have some sample data where we want to add the lengths together.

To do that,

Step 1) Select cell G6.

Step 2) Type in the SUM formula as:

Step 3) Press Enter.

The formula will return the SUM of all lengths given in the range.

You can use the SUMPRODUCT formula to achieve the same result. But since it is a dynamic formula, you will be able to get the total count in one go. No need to find the individual length of each string 🧵

To use the SUMPRODUCT function,

Step 1) Select cell B2.

Step 2) Begin by typing the following formula:

Step 3) Press Enter.

The LEN function counts the length of each cell and sums them all in a single cell.

You can use the SUM function in the same manner but since it is not an array formula, you will have to use the CSE (CTRL + SHIFT + ENTER) keys to convert it to one.

Pretty cool, right? Try it now! 😉

How to count a specific character in a cell

You can also count specific characters in your Excel worksheet using the LEN and SUBSTITUTE functions. The syntax of the formula to count specific characters is as:

where,

reference refers to the cell reference you want to find the length of

character refers to the character you want to look for

”” replaces the found character with an empty string

The overall logic of this function is that the first part of the function counts the number of characters in a cell. The second part finds the given character and replaces it with an empty string.

This reduces the length of the string which is then subtracted from the original length. This returns the count of the specific character in that cell 🥡

It might seem daunting right now, but once you practice it, it’s going to be very easy.

Say, we have the following sample dataset where we want to find the number of occurrences of the character “L”.

To do that,

Step 1) Select cell B2.

Step 2) Begin typing the following formula.

Step 3) Press Enter.

The function counts and returns the number of times the character “L” appeared in the cell, i.e., 2.

To copy the formula to another cell, simply double-click the Fill Handle and it will fill it up 🥤

Pro Tip!

Note that even though there was an “l” present in lowercase in the cell, the formula only returned the count of uppercase “L” as a result. This shows that the SUBSTITUTE function is case-sensitive – so the next time you use the function, make sure to keep this in mind.

Count special characters using UPPER & LOWER functions

We just saw how to count special characters above but that formula was case sensitive. What is if you want to count characters in a cell in case insensitive manner? 🤔

We’ll have to create a new formula for that. Let’s see how to do that below.

Say, I have the following sample data and I want to count all “L” character occurrences present regardless of their case.

To do that,

Step 1) Select a cell.

Step 2) Type in the following formula

Step 3) Press Enter.

What the formula does is that it converts all characters in cell A2 to uppercase and then looks for the desired character. It then returns the count of the times the character appears 2️⃣

Similarly, if you were to count lowercase “l”, you can replace the UPPER function with LOWER as:

If you don’t want to use UPPER or LOWER functions, you can use nested SUBSTITUTE functions too.

In this case, the function first substitutes all occurrences of “L” with an empty string and then does the same with “l”. This way both cases are dealt with and you get the specific character count 😃

How to count specific characters in a range

The basic function for counting specific characters is the same as we did above – we’re just going to make little tweaks to it so it can accommodate an entire array of characters.

Say, we have the following sample data and we want to count the number of times the character “M” appears in the range 🧾

To do that, we will combine the original formula with the SUMPRODUCT function to SUM count as:

Step 1) Select a cell.

Step 2) Type in the following formula.

Step 3) Press Enter.

The function returns the total count of occurrences of the mentioned character in the range 🥇

How to count specific characters in a range using UPPER & LOWER functions

It’s the same as we saw earlier. Combine your formula with UPPER, LOWER or SUBSTITUTE functions accordingly to change their case 🔽

The function converting characters to uppercase would be as:

Function converting characters to lowercase would be as:

Function substituting characters to empty strings would be as:

Pretty simple. no? 🧐

How to count a combination of characters in a string

The formula used to count a combination of characters in a cell is slightly different than the regular one. To know how many times a substring appeared in a given cell, we divide the count by the length of the string 🧵

To find the substring in a case-sensitive formula:

Step 1) Select a cell.

Step 2) Type in the following formula:

Step 3) Press Enter

The function will return the count of the substring as:

Pretty cool, no?

To find the substring in a case-insensitive formula:

Step 1) Select a cell.

Step 2) Type in the following formula:

Step 3) Press Enter

The function will return the count of the substring as:

Try it yourself now! 😀

How to count a substring in a range

Let’s now see how to count the number of occurrences of a substring in a range.

Say, we want to look for the word “Ships” in a range 🔍

To do that,

Step 1) Select a cell

Step 2) Type in the following formula

Step 3) Press Enter

The function returns the count of the word “Ships” in the range.

Note that the formula is case-sensitive as it doesn’t count instances of “ships” but only “Ships”.

If you were to count the characters in the case insensitive formula, you can use the UPPER and LOWER functions as we did before. The formula would look something like this.

With UPPER function 🔼

With LOWER function 🔽

With SUBSTITUTE function

How easy is that? Try it now! 😃

Conclusion

In this tutorial, we saw how to count characters in a cell using the LEN function. We saw different variations of it combined with SUM, SUMPRODUCT, SUBSTITUTE, UPPER functions and more 💪

Not only characters, you can use the same function to find the length of words and count the number of occurrences of a character in ranges too – all by modifying a single base formula.

You can learn more about the LEN function by reading below.

Excel Functions LEFT, RIGHT, MID, and LEN for Substrings

How to Count Cells With Specific Text Using a Formula

How to Use the SUMPRODUCT Function in Excel: With Examples

We hope you enjoyed reading this article as much as we enjoyed creating it.