May 21 2021 02:33 PM
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
May 21 2021 02:43 PM
Solution1) 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.
May 27 2021 01:37 PM
May 27 2021 02:05 PM
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.)
May 27 2021 02:26 PM
See the attached version.
Jan 12 2024 03:47 AM
Jan 12 2024 03:57 AM
Jan 12 2024 04:09 AM
Jan 12 2024 06:05 AM
As described in an earlier reply, you have to select Text Filters > Contains...
A range in column I:
Filter:
Specify Apple:
Result:
Jan 15 2024 07:52 AM
Jan 15 2024 08:48 AM
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)
Jan 16 2024 12:52 AM
Jan 16 2024 01:30 AM
May 21 2021 02:43 PM
Solution1) 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.