With Formulas, Countif Partial String/Substring Match
We can use the COUNTIF function in Excel to count cells by placing an asterisk before and after a partial string or substring.
Select a blank cell to store the counting result, write the formula =COUNTIF(A1:A16,"*Anne*") into it, and press the Enter key (A1:A16 is the range to count cells, and Anne is the partial string).
The total number of cells containing the partial string is then calculated. Take a look at this example:
Note: You can use the formula =COUNTIFS(A1:A16,"*Anne*",A1:A16,"*Green*") to count cells that include both substrings. (The range you'll be counting cells is A1:A16, and Anne and Green are both partial strings.)