Count Characters in a Cell

October 21st, 2004 | Categories: Formulas | Tags: , ,

To count the total characters (including spaces) in cell A1:

=len(A1)

To count the total characters (not including spaces) in cell A1:

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

To count the total specific characters in Cell A1, for instance the number of times the letter “d” appears in cell A1:

=LEN(A1)-LEN(SUBSTITUTE(A1,"d",""))
Leave a comment | Trackback
  1. fathima
    December 8th, 2008 at 07:28
    Reply | Quote | #1

    Why am i unable to use the formula
    =LEN(SUBSTITUTE(A1,” “,”"))
    even if there are characters in A1?

  2. marks
    December 9th, 2008 at 19:46
    Reply | Quote | #2

    @fathima

    Paste that back into excel but replace the quotes. Just delete them and press shift + ‘ on your keyboard. The first three quotes are characters that excel does not recognize.

  3. Rage against the Machine
    February 2nd, 2009 at 17:08
    Reply | Quote | #3

    Thanks the formula worked for me. I think the cell you are pasting the formula in is of type text. Change the format of the Cell to general and retry.