formula to count commas

[Janis]

I have different multiples of 4 character strings delimited by commas in a
column of cells. I just need to count the commas so I can have the number of
multiples of 4 strings. For example, I could have ABCD, in cell one. In
cell 2 I could have ABCD,EFGH, in the next cell. In cell 3 I could have
ABCD,EFGH,IJKL,

I don't know what the string functions would be. But I need to count those
groupings of 4 characters.
Something like this?
count( left(4,","))

thanks,

 

[Peo Sjoblom]

To count commas


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


for range of cells


=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,",","")))

 

[Janis]

mucho gracias

To count commas


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


for range of cells


=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,",","")))

 

[bj]

another way would be
=(len(a1)+1)/5
would give the number of 4 letter groups

 

[Don Guillett]

One way if ONLY groupings of 4

Sub countwords()
tc = 0
For Each c In Range("h1:h6")
If Len(c) > 3 And InStr(c, ",") > 4 Then
x = 1
Else
x = 0
End If
mc = Len(c) - Len(Application.Substitute(c, ",", "")) + x
MsgBox mc
tc = tc + mc
Next c
MsgBox tc
End Sub