How to count characters in Excel cell and range (2024)

Excel formula to count the number of characters in a cell

First things first, let's solve the most common case. To find how many characters there are in an Excel cell, the formula is as simple as:

LEN(cell)

For example, to count characters in each cell of column A beginning in A3, this is what you need to do:

1. Enter the below formula in any empty cell in row 3:

   =LEN(A3)

2. Double-click the fill handle to get the formula copied across the whole column.

Done!

Feel free to use this formula each time you need to count the number of characters in a string.

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

Count characters in a range of cells

To get the total of characters in an Excel range, you can use the LEN function together with SUMPRODUCT:

SUMPRODUCT(LEN(range))

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

=SUMPRODUCT(LEN(A3:A18))

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

=SUM(LEN(A3:A18))

Unlike SUMPRODUCT, the SUM function does not process arrays by default, so you need to press Ctrl + Shift + Enter to turn it into an array formula in Excel 2019 and earlier. In Excel 365 and 2021, it works as a regular formula due to inbuilt support for dynamic arrays.

How this formula works:

The logic is very simple. The LEN function calculates the string length for each individual cell in the specified range and returns an array of numbers. And then, SUMPRODUCT or SUM adds up those numbers and returns the total character count.

How to count specific characters in a cell

To find out how many times a given character appears in a cell, the generic formula is:

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

Suppose you maintain a database of 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 get the number of occurrences of a certain unique identifier in each cell.

Assuming the list of items is in column A beginning in A3, and the target character is in column B in the same row, the formula is as follows:

=LEN(A3) - LEN(SUBSTITUTE(A3, B3, ""))

Note. Excel's SUBSTITUTE is a case-sensitive function, and therefore the above formula treats uppercase and lowercase letters as different characters. For example, cell A4 in the screenshot above contains one occurrence of "a" and two occurrences of "A". The formula counted only the uppercase "A" and returned 2 as the result.

How this formula works:

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

• First, you find the total string length in cell A3 with:
  LEN(A3)
• Then, you remove all occurrences of the letter "A" in A3 by replacing it with an empty string:
  SUBSTITUTE(A3, "A", "")
• The next step is to find the string length without the letter "A":
  LEN(SUBSTITUTE(A3, "A", ""))
• Finally, you subtract the length of the string without "A" from the total length string:
  LEN(A3) - LEN(SUBSTITUTE(A3, B3, ""))

As the result, you get the count of "removed" characters, which is the number of occurrences of that particular character in the cell.

How to count specific characters in a range

Knowing a formula for counting certain characters in a single cell, it's quite easy to modify it a little further to count the number of occurrences of a given character in several cells. For this, just place the LEN formula inside the SUMPRODUCT function that can handle arrays:

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

For example, to get to know how many times the character in D2 occurs in the range A3:A18, the formula is:

=SUMPRODUCT(LEN(A3:A18) - LEN(SUBSTITUTE(A3:A18, D2, "")))

Instead of SUMPRODUCT, you can also use SUM:

=SUM(LEN(A3:A18) - LEN(SUBSTITUTE(A3:A18, D2, "")))

But this formula requires pressing Ctrl + Shift + Enter because, in all versions other than Excel 365 and 2021, SUM can handle arrays only in an array formula.

How this formula works:

The SUBSTITUTE function replaces all occurrences of a given character ("A" in this example) with an empty string ("").

The text string returned by SUBSTITUTE is served to the LEN function so it calculates the string length without A's.

The string length without A's is subtracted from the total length of the original string. The result is an array of character counts per cell.

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

Excel character limits for cells

Microsoft Excel has a limitation on the number of characters that can be entered in a cell. If you have worksheets with large amount of text data, you may find the following information helpful.

• The total number of characters that a cell can contain is 32,767.
• A cell can only display 1,024 characters. At the same time, the Formula bar can show all 32,767 symbols.
• The maximum length of a formula is 8,192 characters in Excel 2007 and higher (1,014 in Excel 2003).

Please consider the facts when you are going to merge or import data from an external source.

Practice workbook for download

Count characters in Excel - formula examples (.xlsx file)