Count Characters in a Cell
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",""))
Why am i unable to use the formula
=LEN(SUBSTITUTE(A1,” “,”"))
even if there are characters in A1?
@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.
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.
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!
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!
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
Wonderful.
thanks it works for me.
in Excel How to count total number of “consecutive” cells have same characters with a selection of cells?
Very Good Job
Genius.
thanx buddy… its working for meee.