Working with cells that contain multiple values delimited by commas

[cswank]

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

[#2 · Accepted solution]

@cswank 

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.

[#3]

@Hans Vogelaar 

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

[#4]

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

[#5]

@cswank 

See the attached version.

[#6]

I have used vb to to populate a column with more than one value but need each one to have a comma afterwards so the filter shows them. How do I do this please?

[#7]

@Michael_Yeats 

What does the VBA code look like?

What is the result currently?

[#8]

Code is below:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$I$" & Target.Row Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & vbNewLine & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

The result populates the cells with multiple selections however, when using filter on the column, it wont pick up cells with more than one selection

[#9]

@Michael_Yeats 

As described in an earlier reply, you have to select Text Filters > Contains...

A range in column I:

Filter:

Specify Apple:

Result:

[#10]

Thanks for the help!

I need Excel to colour code each different value within the multiple values in the same cell. Conditional formatting wont allow this so I'll need to use VB again but don't know where to add the next bit of code.

Current code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$I$" & Target.Row Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & vbNewLine & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

The next bit of code I found that may help is:

Sub ColourPartiaText()
Dim Row As Integer, Col As Integer
Dim CurrentCellText As String
Col = 1
For Row = 2 To 5
CurrentCellText = ActiveSheet.Cells(Row, Col).Value
HotStartPosition = InStr(1, CurrentCellText, "A")
CoolStartPosition = InStr(1, CurrentCellText, "B")
CoolStartPosition1 = InStr(1, CurrentCellText, "C")
CoolStartPosition2 = InStr(1, CurrentCellText, "X")
CoolStartPosition3 = InStr(1, CurrentCellText, "Y")
CoolStartPosition4 = InStr(1, CurrentCellText, "Z")
If HotStartPosition > 0 Then
ActiveSheet.Cells(Row, Col).Characters(HotStartPosition, 1).Font.Color = RGB(255, 0, 0)
End If
If CoolStartPosition > 0 Then
ActiveSheet.Cells(Row, Col).Characters(CoolStartPosition, 1).Font.Color = RGB(255, 0, 0)
End If
If CoolStartPosition1 > 0 Then
ActiveSheet.Cells(Row, Col).Characters(CoolStartPosition1, 1).Font.Color = RGB(255, 0, 0)
End If
If CoolStartPosition2 > 0 Then
ActiveSheet.Cells(Row, Col).Characters(CoolStartPosition2, 1).Font.Color = RGB(51, 153, 51)
End If
If CoolStartPosition3 > 0 Then
ActiveSheet.Cells(Row, Col).Characters(CoolStartPosition3, 1).Font.Color = RGB(51, 153, 51)
End If
If CoolStartPosition4 > 0 Then
ActiveSheet.Cells(Row, Col).Characters(CoolStartPosition4, 1).Font.Color = RGB(51, 153, 51)
End If
Next Row
End Sub

[#11]

@Michael_Yeats 

Replace ColourPartiaText with

Sub ColourPartialText(rng As Range)
    Dim CurrentCellText As String
    Dim Ltr As Variant
    Dim Pos As Long
    CurrentCellText = rng.Value
    For Each Ltr In Array("A", "B", "C", "X", "Y", "Z")
        Pos = InStr(CurrentCellText, Ltr)
        If Pos > 0 Then
            Select Case Ltr
                Case "A", "B", "C"
                    rng.Characters(Pos, 1).Font.Color = vbRed
                Case Else
                    rng.Characters(Pos, 1).Font.Color = RGB(51, 153, 51)
            End Select
        End If
    Next Ltr
End Sub

(I corrected Partia to Partial)

Insert the following line above the last End If in the Worksheet_Change code:

        Call ColourPartialText(Target)

[#12]

That's great, thanks for your help.

I'll have a go at it this morning.

[#13]

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.