As everyone knows, Excel is specifically made for the execution of computational operations. Thus, you can manage to perform other counting operations with numbers. However, this app is helpful when it comes to doing something with text. Here you will get to know how to count characters in Excel by using different formulas and options given in Excel.
While dealing with a text data set, you will see awesome text functions to work on. At times, you may need to compute the total number of characters available in a cell or range. Or else, you may have to count the sum of specific characters that appear in a cell.
LEN function uses to count the total number of characters given in a cell. Apart from that, Excel has numerous options for multiple functions.
How to Count Characters in Excel
As already mentioned, the LEN function is used for counting characters, and the formula =LEN(cell) for counting characters in a cell. Microsoft Word gives you the exact statistics of the file you are working on. The exact number of pages, words, characters (with or without spaces), paragraphs, and lines are given in a Word document. Well, in the case of Excel, finding these details need you to perform some operations. You may have to use a couple of functions simultaneously such as SUMPRODUCT and LEN for counting the total number of characters given in a cell or range using the formula =SUMPRODUCT(LEN(range)).
Furthermore, you will see the total number of specific characters given in a cell or range in this article.
- Count Total Characters in a Cell
- Count Total Characters in a Range
- Count Specific Characters in a Cell
- Count Specific Characters in a Range
- Count Specific Combination of Characters in a Cell or Range
Count Total Characters in a Cell
LEN function is used when you need the total number of characters given in a cell. One argument is used in the LEN function and it could be the double-quoted text or the cell reference to a cell having the text.
Suppose you have a data set as shown below and you need the total number of characters available in each cell.
Below you can see the formula for this operation:
=LEN(A2)
The LEN function by itself might not look alike but when you put it with other formulas, you will see wonders. For instance, you can find out the word count in a cell or split first and last names.
Also, note that the LEN function is able to count the total character in a cell. It does not matter at all whether it is a special character, number, punctuation mark, or space characters such as trailing, leading, and double spaces between words.
If your file has extra spaces in a cell, despite using the LEN function you may get the wrong results. For instance, in the case given below, the LEN function returns 25 for the text in cell A1 whereas it must be 22. Since extra space characters are in the counting that’s why you didn’t get the right results.
If you don’t want to count the extra space characters, you have to apply the TRIM function that helps in removing all leading, trailing, and double spaces present in the cell. Later on, you have to apply the LEN function to get the real outcome.
Here is the formula for this:
=LEN(TRIM(A2))
Count Total Characters in a Range
When a defined range needs the total number of characters, you ought to use a combo of two functions, SUMPRODUCT and LEN. When you are having more than one cell or array, the SUMPRODUCT function is the pro solution for this. Have a look at the given example and see how these two functions work:
=SUMPRODUCT(LEN(range))
=SUMPRODUCT(LEN(B3:B6))
Whereas the LEN function is not an ideal solution for the sum of cell range values as in (=LEN (B3) +LEN (B4) +LEN (B5) +LEN (B6)). Notwithstanding, the SUMPRODUCT function lets you summarize the outcomes of the LEN function given in the defined range. When you put it in the formula, the result will appear as:
=SUMPRODUCT({14;24;22;15})
You can see the numbers from the array derived from the LEN function results that each cell has in the defined range B3:B6 which gives a final character count of 75.
When you need another alternative to work with ranges, SUM and LEN functions give the best combo results. Don’t worry about the syntax as it is almost similar. The only difference is the placement of curly brackets when you need the SUM function in the array formula. Using the keyboard, you can create an array by pressing CTRL + SHIFT + ENTER keys.
Below is the formula that shows the right syntax for the SUM/LEN function combo:
={SUM(LEN(B3:B6))}
Count the Specific Character in a Cell
Counting all the characters in a cell could be simple but when you need to count specific characters that appear in your cell, you have to apply some different methods. For this, you should choose the cell from which you need to count the number of characters and enter this formula:
=LEN (A1) – LEN (SUBSTITUTE (A1, ”a”,””))
Here you have to replace cell A1 with the reference cell and a with the character you need to get the count of. In this case, we tend to find how many times a appears in cell A1.
By clicking on another cell, you will get the count of specified characters.
Count Specific Characters in a Range
SUMPRODUCT function in the presence of other functions provides real-time results when you work with cell ranges and arrays. When it comes to counting specific characters in a range, you need to apply a combo of three functions, SUMPRODUCT, LEN, and SUBSTITUTE. For case insensitive counting, you must have additional formulas: UPPER/LOWER.
At first, you will see the total number of characters “S” in a defined range and remember that it is a case insensitive version. This formula will be the same as the formula explained counting characters. You will see the difference between the LEN function nested in the SUMPRODUCT function and in the counting area rather than the cell is defined range of cells:
=SUMPRODUCT(LEN(B3:B6)) –SUMPRODUCT (LEN(SUBTITUTE(B3:B6;B9;””)))
In a defined range, when you need the counting of case insensitive characters, you may use a thoroughly explained function in a specific cell. Here you will notice two major differences: nesting LEN function in SUMPRODUCT function and replacing the cell with cell range:
=SUMPRODUCT (LEN(B3:B6)) – SUMPRODUCT (LEN(SUBSTITUTE(SUBSTITUTE(B3:B6;LOWER(B9);””);UPPER(B9);””)))
Count Specific Combination of Characters in a Cell or Range
You can even count specific character combo in a defined cell or range. You will use the same formula for counting the combo of characters. Here, in this case, you will need to divide it by the number of characters in the character combo.
Let’s have a look at the example given below: (case insensitive version)
=(LEN(B3) – LEN(SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);””)UPPER(B6);””)))/LEN(B6)
Count Specific Combination of Characters in a Cell or Range
Would you like to count specific character combinations in a defined cell or range?
Previously, you get to know how you can count a single character in a cell or range. Here, in this case, the formula will be the same with a little bit different and that is to divide it by the number of characters given in the character combination.
Let’s have a look at the example:
=(LEN(B3) – (LEN(SUBTITUTE(SUBTITUTE(B3;LOWER(B6);””)UPPER(B6);””)))/LEN(B6)
You will use the same trick that you use for counting a single character. Use the formula: LEN(“in”). without dividing the formula with LEN(“in”), and the outcome will be multiplied by the number of characters in the character combination. In this case, “in” has two characters.
For case sensitive version, when you need to count the specific combination of characters in a cell, the formula will be as:
=LEN(B3) – LEN(SUBTITUTE(B3;B6;””)))/LEN(B6)
When you are counting a combination of characters in a range, the logic will be as same as counting a single character. The formula will be for case sensitive as:
=)SUMPRODUCT(LEN(B3:B6)) – SUMPRODUCT(LEN(SUBSTITUTE(SUBSTITUTE(B3:B6;LOWER(B9);””);UPPER(B9);””))))/LEN(B9)
