Counting the occurrences of a substring in a cell

The formulas in the preceding section count the number of occurrences of a particular character
in a string. The following formula works with more than one character. It returns the number of
occurrences of a particular substring (contained in cell B1) within a string (contained in cell A1).
The substring can consist of any number of characters.
=(LEN(A1)-LEN(SUBSTITUTE(A1,B1,””)))/LEN(B1)
For example, if cell A1 contains the text Blonde On Blonde and B1 contains the text Blonde, the
formula returns 2.
The comparison is case sensitive, so if B1 contains the text blonde, the formula returns 0. The fol-
lowing formula is a modified version that performs a case-insensitive comparison:
=(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(B1),””)))/LEN(B1)