Microsoft Office Tutorials and References

In Depth Information

**Counting Characters in a Cell**

Counting Characters in a Cell

This tip contains formula examples that count characters in a cell.

Counting all characters in a cell

The LEN function returns the number of characters in a cell. For example, if cell A1 contains the

text Inventory, the following formula returns 9:

=LEN(A1)

When you use the LEN function with a value, it returns the number of characters in the number —

which might be different from the number of characters displayed as a result of number formatting.

Counting specific characters in a cell

The following formula counts the instances of
B (uppercase only) in the string in cell A1:

=LEN(A1)-LEN(SUBSTITUTE(A1,”B”,””))

This formula works by using the SUBSTITUTE function to create a new string (in memory) that

has all instances of
B removed. Then the length of this string is subtracted from the length of the

original string. The result reveals the number of occurrences of
B in the original string.

The comparison is case sensitive. So, for example, if cell A1 contains the text Bubble Chart, the

formula returns 1.

The following formula is a bit more versatile. It counts the number of Bs (both upper- and

lowercase) in the string in cell A1.

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),”B”,””))

If cell A1 contains the text Bubble Chart, the formula returns 3.

Counting the occurrences of a substring in a cell

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)