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

Question

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!

Answer 1

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'"))