Spreadsheet Practice

How to count a specific character in a cell in Excel

Formula (Excel and Google Sheets)

=LEN(A2)-LEN(SUBSTITUTE(A2,$E$1,""))

Subtract the length without the character from the full length: =LEN(A2)-LEN(SUBSTITUTE(A2,$E$1,"")).

Result on the example
one result per row in B2, filled down to B9
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in B2, filled down to B9. Change any input and the result updates, or edit the formula to experiment.

How it works

SUBSTITUTE removes every copy of the character, and the text gets one character shorter for each copy. So the full length minus the length without the character is the count. "/products/desks/standing" loses 3 characters, so it has 3 slashes, which is also how deep the page sits.

$E$1 is locked so every row looks for the same character.

=LEN(A2)-LEN(SUBSTITUTE(A2,$E$1,""))

Result1

  1. A2"/about"

    The value in A2.

  2. LEN(A2)6

    LEN: Number of characters.

    • textA2"/about"
  3. $E$1"/"

    The value in E1.

  4. SUBSTITUTE(A2,$E$1,"")"about"

    SUBSTITUTE: Replaces matching text.

    • textA2"/about"
    • old_text$E$1"/"
    • new_text""
  5. LEN(SUBSTITUTE(A2,$E$1,""))5

    LEN: Number of characters.

    • textSUBSTITUTE(A2,$E$1,"")"about"
  6. LEN(A2)-LEN(SUBSTITUTE(A2,$E$1,""))1

    The - operator subtracts.

In Google Sheets

The same formula works in Google Sheets: =LEN(A2)-LEN(SUBSTITUTE(A2,$E$1,"")). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER($E$1),""))1, 2, 3…Ignores case, for counting letters: SUBSTITUTE alone treats "S" and "s" as different.

Common mistakes

  • SUBSTITUTE is case-sensitive: counting "s" misses "S". Lowercase both sides when counting letters.
  • For a word or several characters, divide by its length: =(LEN(A2)-LEN(SUBSTITUTE(A2,"ab","")))/LEN("ab").
  • This counts occurrences inside one cell. To count the cells that contain the character, use =COUNTIF(A2:A9,"*"&E1&"*").

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Count a specific character in a cell

In B2, count how many times the character in E1 appears in the path, then fill down to B9.

Questions

How do I count the character across a whole column?

Wrap it in SUMPRODUCT: =SUMPRODUCT(LEN(A2:A9)-LEN(SUBSTITUTE(A2:A9,E1,""))).

How do I count spaces, or words?

Spaces: use " " as the character. Words in a single-spaced sentence are the spaces plus 1: =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1.

How do I count the commas, to count the items in a list?

Count the commas and add one: =LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.