Counting the number of instances of text in Excel

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

1. 1. Use the function LEN (DLSTR) to find out the number of characters in a string. In the picture below, there are 25 of them, including spaces.

      =LEN(A1)

      =ДЛСТР(A1)

1. Function SUBSTITUTE (SUBSTITUTE) replaces existing text in a string with new text.

   =LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))

   =ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;B1;""))

   

   Explanation:

   • Expression DLSTR(SUBSTITUTE(A1;B1;””)) = 13 is the length of the string without the word “dog”.
   • If we subtract this number from 25, we get the total length of the words “dog” (25-13 = 12).
2. Divide this number by the length of the word “dog” (3), and we get the number of words in the line (12/3 = 4).

   =(LEN(A1)-LEN(SUBSTITUTE(A1,B1"")))/LEN(B1)

   =(ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;B1;"")))/ДЛСТР(B1)