## Example

## Generic Formula

**Text**– This is the text string in which you want to count the occurrences of a given single character.**Character**– This is the character which you want to count.

## What It Does

This formula will count the number of occurrences of a given single character within a text string in a single cell. This formula is case sensitive and will consider upper and lower case letters to be different characters.

## How It Works

The formula takes the difference between these two items:

- Character count of the full text string. This is given by the
**LEN(Text)**part of the formula. - Character count of the text string with all the characters we’re trying to count removed. This is given by the
**LEN(SUBSTITUTE(Text,Character,””))**part of the formula.

In our example **LEN(“Apples and bananas”)** results in **18** since there are 18 characters in the string.

In our example **SUBSTITUTE(“Apples and bananas”,”a”,””)** results in the text string **“Apples nd bnns”**, where each occurrence of “a” has been removed. The character count of this new text string is then found by **LEN(“Apples nd bnns”)** and this results in **14** since there are 14 characters in this string.

The difference of **LEN(Text)-LEN(SUBSTITUTE(Text,Character,””))** is just the count of all our removed characters from the text string. In our example this is **18-14**, so there are **4** “a” characters in the text string!

## 0 Comments