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.
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?
- 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 selectionAs described in an earlier reply, you have to select Text Filters > Contains...
A range in column I:
Filter:
Specify Apple:
Result: