Thanks to the LENGTH formula, you can easily count characters in Excel spreadsheets. It doesn't matter whether this should only happen in a specific cell or for an area. In the following we will explain how to use the formula for character counting correctly.
You can read on here for more tips about controls and formulas in Excel.
Count the total number of characters in a cell
Total characters in a cell can easily be found with the Excel LENGTH function. This function has only one argument (cell reference or text) for which the total number of characters is to be counted:
= LENGTH (text)
= LENGTH (D2)
This function counts the total number of characters in a cell, including spaces, punctuation marks, and symbols, regardless of how often they appear in a string..
Count specific characters in a cell
In addition to the total number of characters, Excel also offers the option of counting the number of occurrences of certain characters. Let's go through the example of counting the number of individual characters in a given cell. A combination of the LENGTH and ALTERNATE functions is required for this purpose. So the formula syntax will look like this:
= LENGTH (cell) -LENGTH (CHANGE (cell, "character", ""))
In a certain example, if we want to count the number of characters a in a defined cell D2, the formula looks like this:
= LENGTH (D2) -LENGTH (CHANGE (D2; "a"; ""))
We briefly explain the logic of the combination of functions. The total number of characters in a cell D2 is subtracted with the number of characters in the same cell, but without the specific character we want to count. The function SUBSTITUTE is used here to change a character string in a defined cell in the form without a specific character and to replace this character with an empty character string.
= CHANGE (cell, "character", "")
The end result shows that the uppercase function does not count because the LENGTH function is case sensitive. The solution for counting characters without considering upper / lower case is to use the UPPER / SMALL function, in which all characters are translated to upper / lower case and the function is no longer case sensitive..
In the example below, the SMALL function is nested inside the SWITCH function, converting all strings in cell D2 to lower case because the criteria are defined as lower case "a":
= LENGTH (cell) -LENGTH (SUBSTITUTE (SMALL (cell), "lowercase", ""))
= LENGTH (D2) -LENGTH (CHANGE (SMALL (D2); "a"; ""))
Count the total number of characters in a range
To count the total number of characters in a defined area, the combination of two functions is required, SUMPRODUCT and LENGTH. Using the SUMPRODUCT function is an elegant solution when we are dealing with multiple cells or arrays. Take a look at the following example and a combination of the formulas:
= SUM PRODUCT (LENGTH (range))
= SUM PRODUCT (LENGTH (D2: D4))
The LENGTH function is cell related, but for ranges of cells, using summed LENGTH functions (= LENGTH (B3) + LENGTH (B4) + LENGTH (B5) + LENGTH (B6)) is not the best solution because you could be dealing with large areas. Instead, the SUMPRODUCT function summarizes the results of the LENGTH function in the defined area. The result of the formula evaluation looks like this:
= SUMPRODUCT ({16; 42; 36})
The numbers from the array are LENGTH function results from each cell in the defined range D2: D4, giving a final result of 94 characters.
Count specific characters in an area
As mentioned earlier, the SUMPRODUCT function is required in combination with other functions to count characters in a range. The combination of three functions is used to count the specific character in an area, taking into account upper and lower case: SUMPRODUCT, LENGTH and CHANGE. Additional formulas should be added for case-insensitive counting: UPPER / SMALL.
First we explain how to count the number of occurrences of the character "a" in a defined area (version that is case-sensitive). This is almost similar to counting specific characters in a cell. The difference here, however, results from the LENGTH function, which is nested in the SUMPRODUCT function and in the counting area, instead of the cell, a defined range of cells is defined:
= SUMPRODUCT (LENGTH (D2: D4)) - SUMPRODUCT (LENGTH (CHANGE (D2: D4; "a"; "")))
To count characters in a defined area regardless of case, we can use the fully explained function for counting characters in a cell, but with two changes: nesting the LENGTH function in the SUMPRODUCT function and replacing the cell with the cell range:
= SUMPRODUCT (LENGTH (D2: D4)) - SUMPRODUCT (LENGTH (CHANGE (CHANGE (D2: D4; SMALL ("a"); ""); LARGE ("a"); "")))
