count of comma separated values

**bujji1305** · 06-01-2012, 06:43 AM

hi
i have a data like this in a string.
K,L,M,N,O
I want to count the total values that are separated by comma and store it in a variable i.e. it has to return 5..
Please help me if you can
Thanks,
bujji

**Fotis1991** · 06-01-2012, 06:51 AM

Hi

One way, could be this.

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

**martindwilson** · 06-01-2012, 06:51 AM

=len(a1)-len(substitute(a1,",",""))+1

**stanleydgromjr** · 06-01-2012, 06:52 AM

bujji1305,

If this string is in cell A1:
K,L,M,N,O

Then in cell B1, try:
=IF(RIGHT(A1,1)<>",",LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1,LEN(A1)-LEN(SUBSTITUTE(A1,",","")))

**bujji1305** · 06-01-2012, 07:02 AM

Hi Fotis1991,martindwilson,stanleydgromjr ....... no code is working for me....

**zbor** · 06-01-2012, 07:05 AM

can you be more specific what doesn't work ?

**bujji1305** · 06-01-2012, 08:42 AM

Actually i have a .csv file from which the data is imported to excel report... From .csv, i need to split the semi colon separated values in a column by giving condition at report generator form i gave condition as K,L,M:K i.e., i need to split data in K to k,L,M
so i get this value in a string a in my code as
a=instr(1,splitcol,",")
now i get a value as K,L,M so i want 'a' count to be stored in a value...

**Fotis1991** · 06-01-2012, 08:55 AM

Pls, upload a sample workbook

**stanleydgromjr** · 06-01-2012, 07:33 PM

bujji1305,

Can we have a workbook/worksheet with the strings in column A, and in column B the results you are looking for manually inputed by you?

To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.