How to count repeated characters in Excel

Page 2

Save Often

Click here to reveal answer

If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

The tutorial explains how to count characters in Excel. You will learn the formulas to get the total character count in a range, and count only specific characters in a cell or in several cells.

Our previous tutorial introduced the Excel LEN function, which allows counting the total number of characters in a cell.

The LEN formula is useful on its own, but in liaison with other functions such as SUM, SUMPRODUCT and SUBSTITUTE, it can handle far more complex tasks. Further on in this tutorial, we are going to have a closer look at a few basic and advanced formulas to count characters in Excel.

How to count all characters in a range

When it comes to counting a total number of characters in several cells, an immediate solution that comes to mind is to get the character count for each cell, and then add up those numbers:

=LEN(A2)+LEN(A3)+LEN(A4)

Or

=SUM(LEN(A2),LEN(A3),LEN(A4))

The above formulas might work fine for a small range. To count total characters in a bigger range, we'd better come up with something more compact, e.g. the SUMPRODUCT function, which multiplies the arrays and returns the sum of the products.

Here's the generic Excel formula to count characters in a range:

=SUMPRODUCT(LEN(range))

And your real-life formula may look similar to this:

=SUMPRODUCT(LEN(A1:A7))

Another way to count characters in a range is to use the LEN function in combination with SUM:

=SUM(LEN(A1:A7))

Unlike SUMPRODUCT, the SUM function does not calculate arrays by default, and you need to press Ctrl + Shift + Enter to turn it into an array formula.

As demonstrated in the following screenshot, the SUM formula returns the same total character count:

How this range character count formula works

This is one of the most straightforward formulas to count characters in Excel. The LEN function calculates the string length for each cell in the specified range and returns them as an array of numbers. And then, SUMPRODUCT or SUM adds up those numbers and returns the total character count.

In the above example, an array of 7 numbers that represent the lengths of strings in cells A1 to A7 is summed:

Note. Please pay attention that the Excel LEN function counts absolutely all characters in each cell, including letters, numbers, punctuation marks, special symbols, and all spaces (leading, trailing and spaces between words).

How to count specific characters in a cell

Sometimes, instead of counting all characters within a cell, you may need to count only the occurrences of a specific letter, number, or special symbol.

To count the number of times a given character appears in a cell, use the LEN function together with SUBSTITUTE:

=LEN(cell)-LEN(SUBSTITUTE(cell, character,""))

To better understand the formula, consider the following example.

Suppose, you maintain a database of delivered items, where each item type has its own unique identifier. And each cell contains several items separated by comma, space, or any other delimiter. The task is to count how many times a given unique identifier appears in each cell.

Assuming that the list of delivered items is in column B (beginning in B2), and we are counting the number of "A" occurrences, the formula is as follows:

=LEN(B2)-LEN(SUBSTITUTE(B2,"A",""))

How this Excel character count formula works

To understand the formula's logic, let's break it down into smaller parts:

• First, you count the total string length in B2:
  LEN(B2)
• Then, you use the SUBSTITUTE function to remove all occurrences of letter "A" in B2 by replacing it with an empty string (""):
  SUBSTITUTE(B2,"A","")
• And then, you count the string length without "A" character:
  LEN(SUBSTITUTE(B2,"A",""))
• Finally, you subtract the length of the string without "A" from the total length string.

As the result, you get the count of "removed" characters, which equals to a total number of that character occurrences in the cell.

Instead of specifying the character you want to count in a formula, you can type it in some cell, and then reference that cell in a formula. In this way, your users will be able to count occurrences of any other character they input in that cell without tampering with your formula:

Note. Excel's SUBSTITUTE is a case-sensitive function, and therefore the above formula is case-sensitive as well. For example, in the above screenshot, cell B3 contains 3 occurrences of "A" - two in uppercase, and one in lowercase. The formula has counted only the uppercase characters because we supplied "A" to the SUBSTITUTE function.

Case-insensitive Excel formula to count specific characters in a cell

If you need a case-insensitive character count, embed the UPPER function inside SUBSTITUTE to convert the specified character to uppercase before running the substitution. And, be sure to enter the uppercase character in the formula.

For example, to count "A" and "a" items in cell B2, use this formula:

=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),"A",""))

Another way is to use nested Substitute functions:

=LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE (B2,"A",""),"a","")

As you can see in the below screenshot, both formulas flawlessly count uppercase and lower case occurrences of the specified character:

In some cases, you may need to count many different characters in a table, but you may not want to modify the formula each time. In this case, nest one Substitute function within another, type the character you want to count in some cell (D1 in this example), and convert that cell's value to uppercase and lowercase by using the UPPER and LOWER functions:

=LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE(B2, UPPER($D$1), ""), LOWER($D$1),""))

Alternatively, convert both the source cell and the cell containing the character either to uppercase or lowercase. For example:

=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2), UPPER($C$1),""))

The advantage of this approach is that regardless of whether the uppercase or lowercase character is input in the referenced cell, your case-insensitive character count formula will return the right count:

Count occurrences of certain text or substring in a cell

If you want to count how many times a specific combination of characters (i.e. certain text, or substring) appears in a given cell, e.g. "A2" or "SS", then divide the number of characters returned by the above formulas by the length of the substring.

Case-sensitive formula:

=(LEN(B2)-LEN(SUBSTITUTE(B2, $C$1,"")))/LEN($C$1)

Case-insensitive formula:

=(LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),LOWER($C$1),"")))/LEN($C$1)

Where B2 is the cell containing the entire text string, and C1 is the text (substring) you want to count.

For the detailed explanation of the formula, please see How to count specific text / words in a cell.

How to count specific character(s) in a range

Now that you know an Excel formula to count characters in a cell, you may want to improve it further to find out how many times a certain character appears in a range. For this, we will take the Excel LEN formula to count a specific char in a cell discussed in the previous example, and put it inside the SUMPRODUCT function that can handle arrays:

SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(range, character,"")))

In this example, the formula takes the following shape:

=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, "A","")))

And here's another formula to count characters in Excel's range:

=SUM(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, "A","")))

Compared to the first formula, the most obvious difference is using SUM instead of SUMPRODUCT. Another difference is that it requires pressing Ctrl + Shift + Enter because unlike SUMPRODUCT, which is designed to process arrays, SUM can handle arrays only when used in an array formula.

If you don't want to hardcode the character in the formula, you can of course type it in some cell, say D1, and reference that cell in your character count formula:

=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, D1,"")))

Note. In situations when you count the occurrences of a specific substring in a range (e.g. orders beginning with "KK" or "AA"), you need to divide the character count by the substring length, otherwise each character in the substring will be counted individually. For example:

=SUM((LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, D1, ""))) / LEN(D1))

How this character counting formula works

As you may remember, the SUBSTITUTE function is used to replace all occurrences of the specified character ("A" in this example) with an empty text string ("").

Then, we supply the text string returned by SUBSTITUTE to the Excel LEN function so that it calculates the string length without A's. And then, we subtract that character count from the total length of the text string. The result of these calculations is an array of character counts, with one character count per cell.

Finally, SUMPRODUCT sums the numbers in the array and returns the total count of the specified character in the range.

A case-insensitive formula to count specific characters in a range

You already know that SUBSTITUTE is a case-sensitive function, which makes our Excel formula for character count case-sensitive as well.

To make the formula ignore case, follow the approaches demonstrated in the previous example: Case-insensitive formula to count specific characters in a cell.

In particular, you can use one of the following formulas to count specific characters in a range ignoring case:

• Use the UPPER function and enter a character in uppercase:

  =SUMPRODUCT(LEN(B2:B8) - LEN(SUBSTITUTE(UPPER(B2:B8),"A","")))

• Use nested SUBSTITUTE functions:

  =SUMPRODUCT(LEN(B2:B8) - LEN(SUBSTITUTE(SUBSTITUTE((B2:B8),"A",""),"a","")))

• Use UPPER and LOWER functions, type either an uppercase or lowercase char in some cell, and reference that cell in your formula:

  =SUMPRODUCT(LEN(B2:B8) - LEN(SUBSTITUTE(SUBSTITUTE((B2:B8), UPPER($E$1), ""), LOWER($E$1),"")))

The below screenshot demonstrates the last formula in action:

Tip. To count the occurrences of a specific text (substring) in a range, use the formula demonstrated in How to count specific text / words in a range.

This is how you can count characters in Excel using the LEN function. If you want to know how to count words rather than individual characters, you will find a few useful formulas in our next article, please stay tuned!

In the meantime, you can download a sample workbooks with character count formula discussed in this tutorial, and check out a list of related resources at the end of the page. I thank you for reading and hope to see you soon!

You may also be interested in