How to Count Characters in Excel

Counting characters sounds trivial until a data-validation rule, an SMS or tweet limit, or an imported file pushes a cell past its limit and something quietly breaks. Excel has no single "character count" button, but a small set of formulas built around the LEN function covers every case you'll run into.

In this tutorial you'll learn how to count characters in a single cell, across a whole range, with or without spaces, and how to count how many times one specific character appears — including a case-insensitive version.

• Count every character in a cell with LEN
• Count characters without spaces using SUBSTITUTE
• Ignore stray leading, trailing and double spaces with TRIM
• Total characters across a range with SUMPRODUCT or SUM
• Count a specific character in a cell or a range, case-sensitive or not

Count characters in a single cell

To count the characters in one cell, enter the LEN function and refer to the cell. It returns the total number of characters, and you can copy it down to score a whole column.

Count characters without spaces

If a value has one or more spaces and you don't want to count them, combine LEN with SUBSTITUTE. SUBSTITUTE swaps every space for nothing, then LEN counts what's left.

Here SUBSTITUTE removes the space between Hello and World! before the count is taken, so the result drops by one.

Ignore extra spaces with TRIM

Imported data often carries leading, trailing or double spaces that inflate your count. If you only want the "real" characters — single spaces between words, nothing extra — wrap the reference in TRIM before counting.

Count characters in a range of cells

To total the characters across a whole range, pair LEN with SUMPRODUCT. SUMPRODUCT feeds the entire range to LEN as an array, gets a count for each cell, and adds them up.

You can get the same result with SUM instead of SUMPRODUCT:

Count characters in a range without spaces

To count the characters in a range while ignoring spaces, drop SUBSTITUTE inside the same combination. It clears the spaces from every cell before LEN and SUMPRODUCT do their work.

In this example the count is 36 when spaces are included, and it falls once SUBSTITUTE removes them — the difference is exactly the number of spaces in the range.

Count a specific character in a cell

To count how many times one character appears in a cell, combine LEN and SUBSTITUTE. The example below counts the asterisk (*).

The formula works in three quick steps:

1. LEN(A1) counts every character in the cell, asterisks included.
2. SUBSTITUTE(A1,"*","") removes the asterisks, and LEN counts what remains.
3. Subtract the second count from the first — the difference is the number of asterisks.

Count a specific character in a range

To count a specific character across a range, wrap the same idea in SUMPRODUCT and point it at the whole range instead of one cell.

SUMPRODUCT takes the whole range as an array and runs the count on every cell. The first LEN gives the total characters per cell; the second, after SUBSTITUTE removes the asterisks, gives the total without them. Subtract the two and SUMPRODUCT adds up the differences to return the count across the range.

Count a character regardless of case

Frequently asked questions