Can I find the number of letters in a cell?

D

dave_laroche

I am trying to find a method of calculating the number of letters in a
particular cell... I can calculate the number of cells that have letter
entries, but I cannot find how to calculate the number of letters in a cell.
For example:

Cell B12: b,c,d

Cell B12: I would like to be able to develop a formula to get the number '3'
 
M

Mike H

Try this:-

=IF(LEN(TRIM(B12))=0,0,LEN(TRIM(B12))-LEN(SUBSTITUTE(B12,",",""))+1)

Mike
 
R

Ron Coderre

If your cell may contain any combination of letters, numbers, punctuation, etc

Try something like this:

A1: a,b,c,1,23,4, Z,!~,#;;/

B1:
=SUMPRODUCT((CODE(UPPER(MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1),1)),1)))>=65)*(CODE(UPPER(MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1),1)),1)))<=90))

In the above example, B1 returns 4
(abcZ)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
R

Ron Coderre

Shorter version of the formula I posted:

=SUMPRODUCT((CODE(UPPER(MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1),1)),1)))<{65,91})*{-1,1})

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
L

L. Howard Kittle

Or, in the slim chance that your example will always be commas as indicated
and no spaces...

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

HTH
Regards,
Howard
 
Top