CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulagoogle-sheets-apispreadsheet
Fredrik Karlsen
Fredrik Karlsen

Reputation: 23

Count/sum occurrences of word in cell in column - Google Sheets

I have two columns of text/abbreviations i want to summarize in a (or two respectively) tables.

Example:

Column 1: SiteScopeInput

Swap_G09+Remove_U21+Rollout_L07+Swap_L08+Swap_L18+Rollout_L21+Rollout_N35 Remove_U21+Rollout_L07+Swap_L08+Swap_L18+Rollout_L21+Rollout_N35 Swap_G09+Rollout_L07+Swap_L08+Swap_L09+Swap_L18+Rollout_L21+Swap_L26+Rollout_N35 Swap_G09+Remove_U21+Rollout_L07+Swap_L08+Swap_L18+Rollout_L21+Rollout_N35

Here I'd like to summarize how many times words occure - for example: Swap_G09 Remove_U1 Rollout_L07 and so on.

I've tried a combination of ArrayFormula, LEN and REGEXREPLACE but my beginner mind can't wrap around this task.

Any tips? Thanks!

Upvotes: 0

Views: 142

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34180

If you want to treat Swap_G09 etc. as a separate word, then a combination of split, flatten and query should do it:

=ArrayFormula(query(FLATTEN(split(filter(A:A,A:A<>""),"+")),"select Col1,count(Col1) where Col1 is not null group by Col1 label Col1 'Word'"))

enter image description here

Upvotes: 1

Related Questions