Text
Count specific characters in a cell: case-sensitive and case-insensitive
Count how many times a delimiter or letter appears inside a string by comparing original text length against SUBSTITUTE length.
Measure character frequency by deleting the character
When you replace every occurrence of a single character with an empty string "", the string shrinks by 1 character per match. Subtracting the shortened length from LEN(text) gives the exact count.
Because SUBSTITUTE is case-sensitive by default, searching for "A" counts only uppercase A unless both arguments are normalized with UPPER.
Use a single ordinary character as the target. A multi-character target measures characters removed, not the number of matches; Unicode case conversion can also change length, so the ASCII case-insensitive example is not a universal Unicode counter.
| PartCode (A) | TargetChar (B) | Exact Case Count | Case-Insensitive Count |
|---|---|---|---|
| SKU-440-North-B2 | - | 3 | 3 |
| Rev-a-2026-A1 | A | 1 | 2 |
Exact-case vs case-insensitive character counts
In "SKU-440-North-B2" (length 16), removing "-" leaves "SKU440NorthB2" (length 13), yielding 3 hyphens. In "Rev-a-2026-A1", exact SUBSTITUTE finds 1 uppercase "A", whereas UPPER on both sides finds 2.
=LEN(A2) - LEN(SUBSTITUTE(A2, B2, ""))Normalize case with UPPER when letter casing varies
UPPER does not change string length for standard ASCII characters, so LEN(A3) - LEN(SUBSTITUTE(UPPER(A3), UPPER(B3), "")) counts both "a" and "A".
=LEN(A3) - LEN(SUBSTITUTE(UPPER(A3), UPPER(B3), ""))Try the example
Use the original values or change them to test your understanding.