Way to prevent case statement from excluding values?

Question

i'm pretty new to the sql world and i've seem to run into a roadblock. Multiple conditions seems to be a regular question but I couldn't find a thread on my current problem. I'm using google data studio and have a dataset in google sheets that includes multiple terms and I want to know how many times each term shows up. Here's and example of my dataset and current written statement

Sample Dataset from the Column labeled "Answer"

1. mini
2. profanity
3. mini, profanity
4. mini, recorded
5. credit bureau
6. Suspicious

CASE
    WHEN CONTAINS_TEXT(Answer,"Credit Bureau") THEN "Credit" 
    WHEN CONTAINS_TEXT(Answer,"Mini") THEN "Mini"
    WHEN CONTAINS_TEXT(Answer,"Profanity") THEN "Profanity"
    WHEN CONTAINS_TEXT(Answer,"Bankruptcy") THEN "Bank"
    WHEN CONTAINS_TEXT(Answer,"Recorded") THEN "Recorded"
    WHEN CONTAINS_TEXT(Answer,"Suspicious") THEN "SAR"
    ELSE "Other" 
END

This statement works perfectly for cells that ONLY contain these terms. However the problem is that in the dataset there are multiple occurrences of the terms showing up in the same cell. Unfortunately the statement will only count the first term and ignore the others, giving me inaccurate totals. For example, in the mini dataset i provided, even though you see profanity twice, it would only be counted once. Does anyone know any potential workarounds or suggestions? Any help would be appreciated.

Answer 1

Count them in separate columns for each category:

credit: case when contains_text(answer, 'credit bureau') then 1 else 0 end a
mini: case when contains_text(answer, 'mini') then 1 else 0 end),
profanity: case when contains_text(answer, 'profanity') then 1 else 0 end)
bank: case when contains_text(answer, 'bankruptcy') then 1 else 0 end)
recorded: case when contains_text(answer, 'recorded') then 1 else 0 end)
sar: case when contains_text(answer, 'suspicious') then 1 else 0 end)
other: case when not contains_text(answer, 'credit bureau')
      and not contains_text(answer, 'mini')
      and not contains_text(answer, 'profanity')
      and not contains_text(answer, 'bankruptcy')
      and not contains_text(answer, 'recorded')
      and not contains_text(answer, 'suspicious') then 1 else 0 end

Then sum each of those columns to get independent totals for each type.