 Count Characters in a Cell

Back to List of Topics

Home
Let's a you have text data in column A, beginning with row one. You desire to count the number of occurances of the letter A. Enter this formula in B1 (or any column to the right).

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"A",""))

Replace the A with any other character to count occurances of other characters.

To find the number of spaces in a cell.

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

Note that this is, in essence, the same formula count the letter A. A space is a character.

How it works:

=LEN(A1) counts the total number of characters in the cell, including spaces.

SUBSTITUTE(UPPER(A1)," ","") replaces the character (the A or the space) with nothing. so the cell now contains x fewer characters.

LEN(SUBSTITUTE(UPPER(A1)," ","")) Computes the Length of the cell without the character.

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"A","")) subtracts the LEN formula results from the total cell length.

For example: there are 28 total characters in a cell and there are 6 spaces. Replace the six spaces with nothing, there are now 22 characters in the cell. 28 - 22 = 6.

To count the number of words in a cell, simply add one to the number of spaces found:

=LEN(A1)+1-LEN(SUBSTITUTE(UPPER(A1)," ",""))

Once the formula is working correctly, you can copy it down the length of column A and the results for every cell in Column A.

Copyright 2003-2004 by InfoStat, All rights reserved.
Rev 2/2004,

Top solutions@info-stat.com