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",""))

Get LIVE Help
Custom Excel solutions & live Excel help!
► Go to www.ExcelAutomationHelp.com
► Email Now@ExcelAutomationHelp.com
  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

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

  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.

    Thanks!

  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:
    =LEN(A1)-LEN(SUBSTITUTE(A1;”d“;””))
    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

    Wonderful.

  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

    Genius.

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

    thanx buddy… its working for meee.