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'

Try this:-

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

Mike

"dave_laroche" wrote:

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'

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


"dave_laroche" wrote:

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'

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


"Ron Coderre" wrote:

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


"dave_laroche" wrote:

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'

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

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

HTH
Regards,
Howard

"dave_laroche" wrote in message
...
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'