SOLVED

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!!

Colleen

 

4 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@cswank 

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

 

S0435.png

Select Text Filters > Contains...

 

S0436.png

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.)

@cswank 

See the attached version.