In some cases, you may need to calculate the total number of characters in a cell/range or the number of times a specific character occurs in a cell. While there is the LEN function that can count the number of characters in a cell, you can do the rest as well with a combination of formulas (as we will see later in the examples). In this tutorial, I will cover different examples where you can count total or specific characters in a cell/range in Excel.
Count All Characters in a Cell
If you simply want to get a total count of all the characters in a cell, you can use the LEN function. The LEN function takes one argument, which could be the text in double-quotes or the cell reference to a cell that has the text. For example, suppose you have the dataset as shown below and you want to count the total number of characters in each cell:
Below is the formula that will do this:
By itself, the LEN function may not look like much, but when you combine it with other formulas, it can do some wonderful things (such as get the word count in a cell or split first and last name). Note: LEN function will count all the characters in a cell, be it a special character, numbers, punctuation marks, and space characters (leading, trailing, and double spaces between words). Since the LEN function counts every character in a cell, sometimes you may get the wrong result in case you have extra spaces in the cell. For example, in the below case, the LEN function returns 25 for the text in cell A1, while it should have been 22. But since it’s counting extra space characters as well, you get the wrong result.
To avoid extra spaces being counted, you can first use the TRIM function to remove any leading, trailing and double spaces and then use the LEN function on it to get the real word count. Below formula will do this:
Count All Characters in a Range of Cells
You can also use the LEN function to count the total number of characters in an entire range. For example, suppose we have the same dataset and this time, instead of getting the number of characters in each cell, I want to know how many are there in the entire range. You can do that using the below formula:
Let me explain how this formula works. In the above formula, the LEN part of the function takes an entire range of cells and counts the characters in each cell. The result of the LEN function would be: {22;21;23;23;23;31} Each of these numbers represents the character count in the cell. And when you use the SUMPRODUCT function with it, it would simply add all these numbers. Now, if you’re wondering why can’t you use SUM instead of SUMPRODUCT, the reason is that this is an array, and SUMPRODUCT can handle array but the SUM function can not. However, if you still want to use SUM, you can use the below formula (but remember that you need to use the Control + Shift + Enter to get the result instead of a regular Enter)
Count Specific Characters in a Cell
As I mentioned that the real utility of the LEN function is when it’ used in combination with other formulas. And if you want to count specific characters in a cell (it could be a letter, number, special character, or space character), you can do that with a formula combination. For example, suppose you have the dataset as shown below and you want to count the total number of words in each cell. While there is no in-built formula to get the word count, you can count the space characters and then use it to know the total number of words in the cell. Below is the formula that will give you the total number of space characters in a cell:
The above formula counts the total number of space characters and then adds 1 to that number to get the word count. Here is how this formula works:
SUBSTITUTE function is used to replace all the space characters with a blank. The LEN function is then used to count the total number of characters when there are no space characters. The result of the LEN(SUBSTITUTE(A2,” “,””)) is then subtracted from LEN(A2). This gives us the total number of space characters that are there in the cell. 1 is added in the formula and the total number of words would be one more than the total number of space characters (as two words are separated with one character).
Note that in case there are any leading, trailing, or double spaces, you are going to get the wrong result. In such a case, it’s best to use the TRIM function along with the LEN function. You can also use the same logic to find a specific character or word or phrase in a cell. For example, suppose I have a dataset as shown below where I have different batches, where each batch has an alphabet and number to represent it (such as A1, J2, etc.)
Below is the formula that will give you the total number of times a batch with the alphabet A has been created each month:
The above formula again uses the same logic – find the length of the text in a cell with and without the character that you want to count and then take the difference of these two. In the above formula, I have hardcoded the character that I want to count, but you can also put it in a cell and then use the cell reference. This makes it more convenient as the formula would update the next time you change the text in the cell.
Count Specific Characters Using Case-Insensitive Formula
There is one issue with the formula used to count specific characters in a cell. The SUBSTITUTE function is case sensitive. This means that you “A” is not equal to “a”. This is why you get the wrong result in cell C5 (the result should have been 3).
So how can you get the character count of a specific character when it could have been in any case (lower or upper). You do that by making your formula case insensitive. While you can go for a complex formula, I simply added the character count of both the cases (lowercase and uppercase A).
Count Characters/Digits Before and After Decimal
I don’t know why, but this is a common query I get from my readers and have seen in many forums – such as this one Suppose you have a dataset as shown below and you want to count the characters before the decimal and after the decimal.
Below are the formulas that will do this. Count characters/numbers before the decimal: Count characters/numbers after the decimal:
Note that these formulas are meant only for significant digits in the cell. In case you have leading or trailing zeroes or you have used custom number formatting to show more/fewer numbers, the above formulas would still give you significant digits before and after the decimal. So these are some of the scenarios where you can use formulas to count characters in a cell or a range of cells in Excel. I hope you found the tutorial useful! Other Excel tutorials you may like:
Count Unique Values in Excel Using COUNTIF Function How to Count Cells that Contain Text Strings How to Count Colored Cells In Excel Count Unique Values in Excel Using COUNTIF Function How To Remove Text Before Or After a Specific Character In Excel
