CODEWITHSUNDEEP
google-sheetscountgoogle-sheets-formulaflattengoogle-query-language
Arnab Jana
Arnab Jana

Reputation: 11

Excel Formula for counting words with same starting letters

Suppose I have a column of words, in some cases more than 1 word in each cell, separated by a comma or space. I want to calculate the number of words starting with A, B, C...,Z separately.

Upvotes: 0

Views: 83

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(QUERY(IFERROR(FLATTEN(REGEXEXTRACT(SPLIT(A1:A10; ", "); "^."))); 
 "select Col1,count(Col1) where Col1 is not null group by Col1 label count(Col1)''"))

enter image description here

or:

=ARRAYFORMULA(QUERY(IFERROR(UPPER(FLATTEN(REGEXEXTRACT(SPLIT(A1:A10, ", "), "^.")))), 
 "select Col1,count(Col1) where Col1 is not null group by Col1 label count(Col1)''"))

enter image description here

Upvotes: 1

Related Questions