Need to count how many times a specific symbol (like @, #, $, or a comma) appears in an Excel cell? Or maybe you want to count all symbols across a range. Excel doesn’t have a dedicated “symbol counter,” but with a few clever formulas you can get the job done quickly. This guide covers everything from single‑cell counts to advanced wildcard techniques.
1. How to Count a Specific Symbol in a Cell
If you need to know how many times a specific symbol (like a comma, dash, or asterisk) appears in a
single cell, you can use a combination of the LEN and SUBSTITUTE
functions.
The Formula
To count the symbol "@" in cell A1, use:
=LEN(A1)-LEN(SUBSTITUTE(A1, "@", ""))
How It Works:
LEN(A1): Calculates the total length of the original text.SUBSTITUTE(A1, "@", ""): Creates a temporary version of your text with all "@" symbols removed.- Subtraction: By subtracting the length of the "clean" text from the original, the remaining number is the count of that specific symbol.
Note: The SUBSTITUTE function is case‑sensitive. If you are counting
letters instead of symbols, ensure your formula matches the case you're looking for.
2. Counting Symbols Across a Range
If you need to count how many times a symbol appears across multiple cells (e.g., A1 to A10), wrap
the previous formula in SUMPRODUCT.
The Formula:
=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10, "@", "")))
This is much faster than counting each cell individually and adding them up.
3. Counting Cells That Contain Any Symbols
Sometimes you don't want the total count of symbols, but rather a count of how many cells contain at least one symbol or special character.
Using Wildcards
If you want to count cells containing an asterisk (*), you have
to use a "tilde" (~) because the asterisk is a wildcard in Excel:
=COUNTIF(A1:A10, "*~**")
For general symbols, you can use COUNTIF with the symbol surrounded by wildcards:
=COUNTIF(A1:A10, "*@*")
4. Counting All Non‑Alphanumeric Characters
If you need to count “any symbol” (anything that isn’t a letter or number), the formulas get a bit more complex. Usually, this requires a User Defined Function (VBA) or a complex Array Formula.
However, a quick “cheat” for simple data cleaning is to use Flash Fill (Ctrl + E) to extract symbols into a new column and then count those results.
Summary Table: Quick Reference
| Goal | Formula Example |
|---|---|
| Count specific symbol in 1 cell | =LEN(A1)-LEN(SUBSTITUTE(A1, "-", "")) |
| Count specific symbol in a range | =SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10, "-", ""))) |
| Count cells containing a symbol | =COUNTIF(A1:A10, "*#*") |
Pro Tip: Watch Out for Hidden Spaces
If your counts seem off, it might be due to hidden spaces. Use the TRIM function inside
your formula to ensure you are only counting the characters you can actually see:
=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1), "@", ""))
Frequently Asked Questions
- Can I count multiple different symbols at once? Yes, you can nest multiple SUBSTITUTE functions, but it gets messy. For multiple symbols, consider a VBA macro.
- Why does COUNTIF with "*@*" count cells with an "@" anywhere? Because the asterisk (*) is a wildcard that matches any sequence of characters.
- How do I count symbols that are also wildcards (like * or ?)? Use a tilde (~)
before the character, e.g.,
COUNTIF(A1:A10, "*~**")counts cells with an asterisk.
Would you like us to create a VBA macro for you that can detect and count any non‑alphanumeric character automatically? Contact us – we’re happy to help!