Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to add commas to the result of a formula with a text operator? | Excel Discussion (Misc queries) | |||
So many commas | Excel Worksheet Functions | |||
Count values between commas | Excel Discussion (Misc queries) | |||
Count Commas in Cells | Excel Discussion (Misc queries) | |||
Removing commas | Excel Worksheet Functions |