Working with cells that contain multiple values delimited by commas

New Contributor

I have a column where each cell has up to 5 values (from a multiple choice survey), where possible values are one or more of: A, B, C, D, or E,  and any multiple values in one cell are delimited by commas. 

1) How do I filter out only the rows where the cell value contains a particular value such as  "A"?   (same for B, C, D, E)

2) How do I count how many "A" values there are in a column?


Thank you!!



4 Replies
best response confirmed by allyreckerman (Microsoft)


1) After turning on Filter, click the filter dropdown arrow in the top cel of the column.



Select Text Filters > Contains...



Enter A (or B, C, ...) in the box.

Click OK.


2) Use a formula such as


=COUNTIF($A$2:$A$50, "*A*")


The wildcard characters * tell Excel to count cells that contain A with possibly other text.

@Hans Vogelaar 


Thank you very much for your response. Very Helpful!!

@Hans Vogelaar 


How do you take a column of data, each cell having one or more text values (A,B,C,D,E), and then show the distribution of responses on a bar graph?  


Sample column data and sample bar graph are attached. (The bar graph was created by google forms, but I want to know how to create my own from my data in an Excel file.)


See the attached version.