Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to count a specific character in a cell in Excel and Google Sheets
Excel & Google Sheets
=LEN(A2)-LEN(SUBSTITUTE(A2,"x",""))✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| header | header |
| example text | another example |
| xxx | xx |
| test | testing |
=LEN(A2)-LEN(SUBSTITUTE(A2,"x",""))→2
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 cell where you want the count to appear.
- 2Enter the formula using LEN and SUBSTITUTE functions.
- 3Replace 'A2' with your target cell reference and 'x' with the character you want to count.
- 4Press Enter to see the result.
Tips
- Ensure the character you're counting does not appear in the formula itself.
- This method is case-sensitive; adjust the character in the formula accordingly.
- Test the formula with different characters and cell contents to ensure accuracy.
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
Can I count spaces?
Yes, replace 'x' in the formula with a space (' ').
What if the character appears consecutively?
The formula counts each occurrence, including consecutive ones.
Does this work with special characters?
Yes, any character can be counted, including special characters like '@', '#', etc.