formula to count commas
 

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,

formula to count commas
 

To count commas


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


for range of cells


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


--
Regards,

Peo Sjoblom



"Janis" wrote in message
...
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,

formula to count commas
 

mucho gracias

"Peo Sjoblom" wrote:

To count commas


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


for range of cells


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


--
Regards,

Peo Sjoblom



"Janis" wrote in message
...
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,

formula to count commas
 

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

"Janis" wrote:

mucho gracias

"Peo Sjoblom" wrote:

To count commas


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


for range of cells


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


--
Regards,

Peo Sjoblom



"Janis" wrote in message
...
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,

formula to count commas
 

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Janis" wrote in message
...
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,