Text
Count specific characters across a range of cells
Count every hashtag, comma, or letter across an entire column at once by combining TEXTJOIN, LEN, and SUBSTITUTE.
Why COUNTIF("*#*") undercounts cells with multiple hashtags
COUNTIF(A2:A5, "*#*") counts how many CELLS contain at least one #, returning 3 even when row 2 holds three hashtags ("#seo #growth #b2b") and row 5 holds two ("#cloud #ai").
Joining A2:A5 into a single string via LET(all, TEXTJOIN("", TRUE, A2:A5), ...) and subtracting LEN(SUBSTITUTE(all, B2, "")) counts all 6 individual # characters across the range.
Use a single-character target. A multi-character target counts removed characters and can match across joined cell boundaries. The COUNTIF comparison requires wildcard escaping for literal *, ? or ~. Keep joined text within Excel’s cell-length limit; use helpers for larger data.
| TagCell (A) | # Count in Row | COUNTIF "*#*" |
|---|---|---|
| #seo #growth #b2b | 3 | 1 cell |
| #saas | 1 | 1 cell |
| untagged | 0 | 0 cells |
| #cloud #ai | 2 | 1 cell |
Range-wide character frequency formula
Comparing total character occurrences (6) against COUNTIF cell count (3) immediately reveals average tags per tagged post (6 / 3 = 2).
=LET(all, TEXTJOIN("", TRUE, A2:A5), LEN(all) - LEN(SUBSTITUTE(all, B2, "")))Case-insensitive letter count across a range
Normalize both all and TargetChar with UPPER when counting letters regardless of capitalization.
=LET(all, UPPER(TEXTJOIN("", TRUE, A2:A5)), LEN(all) - LEN(SUBSTITUTE(all, UPPER(B2), "")))Try the example
Use the original values or change them to test your understanding.