Counting the frequency of a text string

[Michael Styles]

I've inherited some data to work with that I'm not sure how to approach.
Basically, multiple answers are contained within a cell. I need to extract
a sum of each individual answer. The data looks like this:


Special Education Students;#Economically Disadvantaged Students;#Minority
Students;#Regular Classroom Students;#Gifted and Talented Students
Special Education Students;#Economically Disadvantaged Students;#Minority
Students
Special Education Students;#Economically Disadvantaged Students;#Minority
Students;#Regular Classroom Students;#Gifted and Talented Students
Special Education Students;#Economically Disadvantaged Students;#Regular
Classroom Students
Special Education Students;#ESOL Students;#Regular Classroom Students
Special Education Students

I need to know how often "Special Education Students" occurs in 70 rows of
data; how often "Minority Students" occur, how often "Regular Classroom
Students" occur, etc...

I've tried CountIf but that only works if the cell only contains "Special
Education Students"

Any assistance would be greatly appreciated.

 

[Dave Peterson]

You can use wildcards (if the string you're looking for doesn't occur too far
into the cell (past the first 255 characters):

=COUNTIF(A:A,"*"&"Special Education Students"&"*")

Or

=COUNTIF(A:A,"*"&B1&"*")
if B1 contains: Special Education Students

=countif() will not find the substrings if they occur after the first 255 (0r
256???) characters.

 

[Michael Styles]

Perfect. Thanks for your quick input!

You can use wildcards (if the string you're looking for doesn't occur too
far
into the cell (past the first 255 characters):

=COUNTIF(A:A,"*"&"Special Education Students"&"*")

Or

=COUNTIF(A:A,"*"&B1&"*")
if B1 contains: Special Education Students

=countif() will not find the substrings if they occur after the first 255
(0r
256???) characters.