Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to count how many times a character appears in a cell in Excel and Google Sheets
Excel & Google Sheets
=LEN(A2)-LEN(SUBSTITUTE(A2,"a",""))✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Sentence |
| banana split |
| abracadabra |
| hello world |
| aardvark |
| spreadsheet |
=LEN(A2)-LEN(SUBSTITUTE(A2,"a",""))→3
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Select the output cell.
- 2Type =LEN(A2)-LEN(SUBSTITUTE(A2,"a","")) — replacing "a" with the character you want to count.
- 3Press Enter. The formula works by removing the target character and comparing the length difference.
- 4To count case-insensitively, wrap both LEN arguments with LOWER: =LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),"a","")).
- 5To count a multi-character substring, divide by the length of the substring: =(LEN(A2)-LEN(SUBSTITUTE(A2,"ab","")))/LEN("ab").
Tips
- SUBSTITUTE is case-sensitive by default. Use LOWER() or UPPER() around the cell reference to make it case-insensitive.
- This technique works for substrings too — just replace the single character with a longer string.
- Counting spaces tells you the number of words minus 1 in a single-word-per-space sentence.
- For a range, wrap in SUMPRODUCT: =SUMPRODUCT(LEN(A2:A6)-LEN(SUBSTITUTE(A2:A6,"a",""))).
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Why divide by LEN of the substring?
Each removal of a two-character substring 'ab' shrinks the string by 2, not 1. Dividing by LEN("ab") corrects for this.
Does this work for counting spaces (words)?
Yes. =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1 gives an approximate word count.