Hi There, I have a column with comma separated strings. How to find the occurrence of each string?

Galla Ramulu

Please see the below table. I have 20,000 responses. I need to find out how many times each response was given.

I have 20000 responses. I need to find out how many times each response was given.

Thank You.

Paul Newcome

Is there a possibility that a response could be duplicated within the same cell?

For example...

agree, agree, disagree, rather agree

Galla Ramulu

No, responses could not be duplicated within the same cell.

Genevieve P.

Hi @Galla Ramulu

Since your values contain the same phrasing (ex. the word "agree" appears within the word "disagree") it makes counting each individual instance a little trickier.

I would add a helper column next to this column to evaluate the values on a cell-by-cell basis, returning each of the values that appear within commas as an individual value in a multi-select column, like so:

=SUBSTITUTE(String@row, ",", CHAR(10))

Now that your values are in a Multi-Select column, we can use the HAS function to see how many times a unique value is in this column, like so:

=COUNTIF([Multi-Select Column]:[Multi-Select Column], HAS(@cell, "agree"))

The HAS function makes sure that it doesn't count the "agree" in "disagree", but searches for the exact selection instead.

Will this work for you?

Cheers,

Genevieve

Paul Newcome

@Galla Ramulu My apologies. I missed the alert from your reply. I would make the same suggestion as Genevieve above.

@Genevieve P. Thanks for stepping in!

Genevieve P.

Always good to know I'm on the right track!! Thanks Paul 🙂

Paul Newcome

@Genevieve P. That's why I asked if there could be duplicates within the same cell. I wanted to use this method with the multi-select and HAS functions, but if the same response could be duplicated within a single cell then it would not work as the multi-select would filter out the duplicate and you wouldn't get an accurate count.

@Galla Ramulu One thing to note... I just noticed in your screenshot there are a couple of entries that do not have a comma separating them. This will skew the end results. The only place I noticed this in your screenshot is when the last two entries are "rather agree" and "moderate" in that order. Those few rows have

"rather agree moderate"

instead of

"rather agree, moderate"