Current Events > Help separating and organizing survey responses in Sheets/Excel

(+) Yeah! (+)

My work took a survey about units of weight and I need to do a simple comparison of how people used them by frequency. They don't really need any statistical analysis other than a raw list that shows (for instance: Kg - 5 times; KG - 2 times; kg - 6 times; kg. - 3 times; kilos - 9 times), but it's been such a pain in the ass so far.

Each cell represents three answers one person gave. I need to separate them out (text to columns, easy enough), then count the unique tokens of units.

Is there a better way than just taking that long list and manually tallying kg 1st instance, kg 2nd instance, kg 3rd instance... I'm willing to do it, but it's tedious, untidy, prone to error and just seems like a piece of scripting someone would have figured out by now.

Integers don't matter for token frequency (200kg = 1kg = 28kg), punctuation and caps do (kg =/= Kg =/= kg.).


The dumb way:


---

Can you do a Countif function for cells that contain each of the ways they're worded?
Like
=COUNTIF(A1:B10,"*"&"kg"&"*")
=COUNTIF(A1:B10,"*"&"KG"&"*")
etc

Thanks! It doesn't account for case. I know only a little about Excel and I'm not sure if there's a basic add-in for doing that.

I'm having decent luck in terms of speed with the dirty way, but I definitely need to learn more of this stuff for the future. I'm a dang luddite.

---

Good point, I forgot about the case. I think the Find function is case sensitive though, maybe try:

=COUNT(FIND("Kg", A1:B10))