How to calculate Number of Instances in Excel

by

This example describes how to count the number of instances of text (or a number) in a cell.

1. Use the LEN function to get the length of the string (25 characters, including spaces).

Worked Example:   Find Last Day of the Month in Excel

2. The SUBSTITUTE function replaces existing text with new text in a string. LEN(SUBSTITUTE(A1,B1,””)) equals 13 (the length of the string without the words dog). If we subtract this number from 25, we get the length of the dog instances (25-13=12).

Worked Example:   Understanding Named Range, Named Constant, Name Manager in Excel Formulas
Worked Example:   Extract last name from full name -- Manipulating NAMES in Excel

3. Dividing this number by the length of the word dog (3), gives us the dog instances (12/3=4).

Leave a Reply

Your email address will not be published. Required fields are marked *