Count Characters in a Cell

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

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


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


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

  1. 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


    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.

  4. November 30th, 2010 at 17:43
    Reply | Quote | #4

    That was very helpful. I used it to filter a string of numbers that had to be of so many numbers (5 and 4 respectively). Using the LEN command, I could then filter from smallest to greatest and concatonate zeroes to the end of the numbers that were fewer than four or five digits.


  5. December 26th, 2010 at 06:12
    Reply | Quote | #5

    Please check to see if your spreadsheet program uses , or ; as a seperator between arguments. With open office, the correct syntax of the expression is to use ; as shown below:
    When in doubt, let the program build the expression and add the arguments in the proper locations.
    This information was VERY helpful in what I was trying to accomplish!

  6. Sarath Chandran
    January 29th, 2011 at 14:49
    Reply | Quote | #6

    How to add the total numbers in a cell. For eg if in the cell A1 the number is 98956, i want to add 9+8+9+5+6
    the total will be 37

  7. February 8th, 2011 at 06:31
    Reply | Quote | #7


  8. March 19th, 2011 at 17:21
    Reply | Quote | #8

    thanks it works for me.

  9. Khan
    April 10th, 2011 at 03:31
    Reply | Quote | #9

    in Excel How to count total number of “consecutive” cells have same characters with a selection of cells?

  10. April 25th, 2011 at 09:37

    Very Good Job

  11. Duke
    June 27th, 2011 at 15:16


  12. zafnim
    November 21st, 2011 at 07:25

    thanx buddy… its working for meee.