SOLVED

Working with cells that contain multiple values delimited by commas

%3CLINGO-SUB%20id%3D%22lingo-sub-2377051%22%20slang%3D%22en-US%22%3EWorking%20with%20cells%20that%20contain%20multiple%20values%20delimited%20by%20commas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2377051%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20column%20where%26nbsp%3Beach%20cell%20has%20up%20to%205%20values%20(from%20a%20multiple%20choice%20survey)%2C%20where%26nbsp%3Bpossible%20values%20are%20one%20or%26nbsp%3Bmore%20of%3A%26nbsp%3BA%2C%20B%2C%20C%2C%20D%2C%20or%20E%2C%20%26nbsp%3Band%20any%20multiple%20values%20in%20one%20cell%20are%20delimited%20by%20commas.%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20How%20do%20I%20filter%20out%20only%20the%20rows%20where%20the%20cell%20value%20contains%20a%26nbsp%3Bparticular%20value%20such%20as%20%26nbsp%3B%22A%22%3F%26nbsp%3B%26nbsp%3B%20(same%20for%20B%2C%20C%2C%20D%2C%20E)%3C%2FP%3E%3CP%3E2)%20How%20do%20I%20count%20how%20many%20%22A%22%20values%20there%20are%20in%20a%20column%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!!%3C%2FP%3E%3CP%3EColleen%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2377051%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2377093%22%20slang%3D%22en-US%22%3ERe%3A%20Working%20with%20cells%20that%20contain%20multiple%20values%20delimited%20by%20commas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2377093%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1060354%22%20target%3D%22_blank%22%3E%40cswank%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1)%20After%20turning%20on%20Filter%2C%20click%20the%20filter%20dropdown%20arrow%20in%20the%20top%20cel%20of%20the%20column.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0435.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F282754iF6C0DFB7FE0B9666%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0435.png%22%20alt%3D%22S0435.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%0A%3CP%3ESelect%20Text%20Filters%20%26gt%3B%20Contains...%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0436.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F282755i359537FF1DA86637%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0436.png%22%20alt%3D%22S0436.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%0A%3CP%3EEnter%20A%20(or%20B%2C%20C%2C%20...)%20in%20the%20box.%3C%2FP%3E%0A%3CP%3EClick%20OK.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E2)%20Use%20a%20formula%20such%20as%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DCOUNTIF(%24A%242%3A%24A%2450%2C%20%22*A*%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20wildcard%20characters%20*%20tell%20Excel%20to%20count%20cells%20that%20contain%20A%20with%20possibly%20other%20text.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2393666%22%20slang%3D%22en-US%22%3ERe%3A%20Working%20with%20cells%20that%20contain%20multiple%20values%20delimited%20by%20commas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2393666%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20your%20response.%20Very%20Helpful!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2393739%22%20slang%3D%22en-US%22%3ERe%3A%20Working%20with%20cells%20that%20contain%20multiple%20values%20delimited%20by%20commas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2393739%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20you%20take%20a%20column%20of%20data%2C%20each%20cell%20having%20one%20or%20more%20text%20values%20(A%2CB%2CC%2CD%2CE)%2C%20and%20then%20show%20the%20distribution%20of%20responses%20on%20a%20bar%20graph%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESample%20column%20data%20and%20sample%20bar%20graph%20are%20attached.%20(The%20bar%20graph%20was%20created%20by%20google%20forms%2C%20but%20I%20want%20to%20know%20how%20to%20create%20my%20own%20from%20my%20data%20in%20an%20Excel%20file.)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2393796%22%20slang%3D%22en-US%22%3ERe%3A%20Working%20with%20cells%20that%20contain%20multiple%20values%20delimited%20by%20commas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2393796%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1060354%22%20target%3D%22_blank%22%3E%40cswank%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.