# How to count a specific character in a cell in Excel

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

**Excel:** `=LEN(A2)-LEN(SUBSTITUTE(A2,$E$1,""))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Page path | Slashes |  | Character | / |
| /about |  |  |  |  |
| /products/desks |  |  |  |  |
| /products/desks/standing |  |  |  |  |
| /blog/2026/03/new-range |  |  |  |  |
| /contact |  |  |  |  |
| /products/chairs/office/mesh |  |  |  |  |
| / |  |  |  |  |
| /blog |  |  |  |  |

Formula in B2, filled to B9: `=LEN(A2)-LEN(SUBSTITUTE(A2,$E$1,""))`

Result: 1; 2; 3; 4; 1; 4; 1; 1

## 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.

## Other ways

- `=LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER($E$1),""))`: 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&"*")`.

## 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`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-a-specific-character-in-a-cell