There are no spaces in the above formula multiple lines are used only to fit the formula into this document. Where cell_ref is the cell reference, and 'a' is replaced by the character you want to count. The value of cell A8 is 4 because the text 'apple' appears four times in the range.įormula to Count the Number of Occurrences of a Single Character in One Cell Start Excel, and then open a new workbook.Ī8: =SUM(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,'apple','')))/LEN('apple') Example 1: Counting the Number of Occurrences of a Text String in a Range This formula can replace all later formulas in this article except the formula to count the number of words in a cell. The formula must be divided by the length of the text string because the sum of the character length of the range is decreased by a multiple of each occurrence of the text string.
To enter a formula as an array in Excel for Macintosh, press COMMAND+RETURN. To enter a formula as an array in Excel for Windows, press CTRL+SHIFT+ENTER. The above formula must be entered as an array formula.
