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)
 
Search JabSto ::




Custom Search