Features of !SEMTools

Count characters inside cells

Counting characters in Excel is often essential — the only question is which approach to use. The right method depends on your scenario and constraints.

When you need to count characters in cells

  • A per-cell character limit must not be exceeded.
  • No per-cell limit, but there’s a total character cap across several cells.
  • A minimum character length is required.
  • You must limit how many times specific characters appear (e.g., no more than one “!” per cell).

Below are practical options for each case.

Character count in a single cell — formula

LEN is the go-to function for counting characters in a cell:

=LEN(A1)
Character count formula result
Result of the formula

Count occurrences of specific characters in a cell

A classic trick: get the length of the original text with LEN, subtract the length after removing the target character(s) with SUBSTITUTE.

Example: count how many times the letter “e” appears in A1:

=LEN(A1)-LEN(SUBSTITUTE(A1,"e",""))

When the set is larger (number characters, all English letters, etc.), use arrays.

Count number characters (0–9) in a cell

Counts each occurrence of 0–9:

=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))

Alternative (no SUBSTITUTE), checks each character and counts numeric ones:

=SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

Count English letters (A–Z) in a cell

Use the ASCII range 65–90 (A–Z) with CHAR and ROW; wrap text with UPPER for case-insensitive counting:

=SUM(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW(INDIRECT("65:90"))),"")))

Count any letters (A–Z plus accented letters)

Letters change when toggling case; non-letters don’t. Compare each character’s UPPER vs LOWER version and count differences:

=SUMPRODUCT(--(UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<>LOWER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))

This counts letters across Latin alphabets, including many accented characters.

Count characters with !SEMTools (no formulas)

If you’d rather skip formulas, the !SEMTools procedure counts characters in a couple of clicks and (optionally) outputs results to the column on the right. Select an entire column if you like — the add-in will detect the used range automatically.

Count characters in multiple cells with !SEMTools
Counting characters for multiple cells with !SEMTools

Sum the total characters across multiple cells

Need a total rather than per-cell counts? Use an array-aware sum of lengths:

Sum of character counts across cells
Formula to sum the lengths across several cells

Formula (Excel 365+):

=SUM(LEN(A2:A6))

Legacy-compatible alternative:

=SUMPRODUCT(LEN(A2:A6))

Like this guide? Support the author — and save time. The !SEMTools add-in solves hundreds of Excel text tasks without complex formulas, so you can work faster.

This post is also available in RU.