How to create a count plot from comma separated values of a column in google sheets?

user_5200329 0
Viewed 218

I was trying to create a count plot from the state names that appear in one of the columns of the google-sheet like following:

The open shareable google sheet link:

https://docs.google.com/spreadsheets/d/10NzbtJhQj4hQBnZXcmwise3bLBIAWrE0qwSus_bz7a0/edit#gid=8060956

Column5
Dublin, OH
Chicago, IL
Chicago, IL, NY, FL
Atlanta,GA, DC,NY
Columbus, OH, NY

How to create the count plots using only the state names?

  • The first word(s) before first comma is always city
  • There can be space after commas.

Required output:
enter image description here

google-sheets-formula google-sheets
1 Answers

What about something like:

enter image description here

Formula in C1:

=QUERY(TRANSPOSE(SPLIT(TEXTJOIN(",",TRUE,A1:A),", ")),"Select Col1, count(Col1) where Col1 matches '[A-Z][A-Z]|Remote' group by (Col1) order by Col1 ASC", -1)
user_97581940
Related