Jun
15

How to count characters in Excel: total or only specific characters in a cell / range

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))
Formula to count a total number of characters in a range

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:
Another formula to count all characters in a range

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:
The SUMPRODUCT function sums the numbers in the array, and returns the total character count.

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",""))
A formula to count specific characters in a cell

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:
Type the character you want to count in some cell, and reference that cell in a 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:
Case-insensitive formula to count specific characters in a cell

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:
Counting how many times a specific character appears in a given cell ignoring case

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.
The formula to count more than one character (text) in a cell

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","")))
A formula to count specific characters in a range

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:
A case-insensitive formula to count specific characters in a range

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:

10 Responses to "How to count characters in Excel: total or only specific characters in a cell / range"

  1. Vinayaka R Shenoy says:

    Hi,
    I have a Question,

    If i Enter a single word in Cell, Entire Row color Should be change.

  2. sriram pydah says:

    How to count specific characters in a range in a specific date as per the above example

  3. anand baldha says:

    How to count numbers in cell Exp:-6,4,9,10=4

  4. ARNAB says:

    Hi,
    Could you please solve my problem?
    I want to count continue a letter.
    Here I am trying to explain that, how many "P" is continue to 6 days.
    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
    P P P P P P P P P P P P P P P P P P P P P P P P

    Result will be : 3. 3 time its count continue 6P.(1 to 6, 13-18,19-24)

    How this is count in excel?if there is any formula,pls help.

  5. Kalyan says:

    Hi, could you please solve the below issue with Macro. I have a Data with different names but not exact match. I want the maxi matched text displayed in other row or column. Match Names1 with Names 2 and seprate the values.

    Please past the below names in excel.
    Names 1 Names 2
    john.nikki 0123-12 jhon-01, nikki
    Rosa 8788-a45- Rosa;R, 56 & German
    Y.Cristina ycristina.All-654.09, As
    ThomasAndrew GR 56-as, Thomas.gr&Andrew.M/for
    All are ok 123 All.are.ok,now @ 123
    Need solution, for Solution for
    examples example.Needsolution

    Thanks in Advance.
    Kalyan Challa

  6. Jibann Puri says:

    Hello, could you please solve the below issue with excel
    i have many more "YES" or "NO" on my Excel Row i wannn to count of how many "YES" or "NO" in the Row Which formula is better on the case.

  7. Mohanam says:

    Hi, How to count the total of a specify letter (exa. N) from efferent columns.

  8. Ian Galyer says:

    Do do i get the text after a number of instances of a unique char
    from
    E:\Data Bases\Access VB and stuff\Cell references and Date and Time.xlsx

    I want to get the text between the 2nd and 3rd "\" and the text after the last "\"
    The answers would be
    1. Access VB and stuff
    2. Cell references and Date and Time.xlsx

    Cheers
    Ian

  9. Ray says:

    For your first example of an array formula, using SUM and LEN, I think you inadvertently reprinted the formula from SUMPRODUCT right before. Shouldn't the formula be {=SUM(LEN(A1:A7))}?

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard