Forum Discussion
Solved
Working with cells that contain multiple values delimited by commas
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 c...
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.
Working with cells that contain multiple values delimited by commas is common, and it often involves tasks like splitting, combining, or analyzing the values. Here are some common scenarios and techniques for dealing with such cells, assuming you are using a spreadsheet software like Microsoft Excel or Google Sheets:
Splitting Values:
Excel:
You can use the "Text to Columns" feature. Select the column, go to the "Data" tab, and choose "Text to Columns." Select the delimited option, choose comma as the delimiter, and follow the wizard.
Alternatively, you can use formulas like LEFT, RIGHT, MID, or combination of these to extract individual values.
Google Sheets:
You can use the SPLIT function. For example, if A1 contains your comma-separated values, you can use =SPLIT(A1, ",") in another cell to split the values.
Combining Values:
Excel:
You can use the CONCATENATE function to combine values. For example, =CONCATENATE(A1, ", ", B1, ", ", C1).
In newer versions of Excel, you can also use the TEXTJOIN function.
Splitting Values:
Excel:
You can use the "Text to Columns" feature. Select the column, go to the "Data" tab, and choose "Text to Columns." Select the delimited option, choose comma as the delimiter, and follow the wizard.
Alternatively, you can use formulas like LEFT, RIGHT, MID, or combination of these to extract individual values.
Google Sheets:
You can use the SPLIT function. For example, if A1 contains your comma-separated values, you can use =SPLIT(A1, ",") in another cell to split the values.
Combining Values:
Excel:
You can use the CONCATENATE function to combine values. For example, =CONCATENATE(A1, ", ", B1, ", ", C1).
In newer versions of Excel, you can also use the TEXTJOIN function.