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",""))).
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
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.