Count Values comma separated non-numeric (Google Sheets)

0
votes

I'm trying to figure out how to get a count of answers inside a cell which are comma-separated in this format: Anna, peter, Hans, Otto (here it should be 4)

Need this for an assignment and nothing seems to work and my programming are very limited so I hope someone might help me out here :/

I have tried it in excel first with this formula:

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

..which didn't work (the brackets around the first A1 and after substitute turned red - whats that telling us anyway? My search only show me entries about negative values..)

Then I tried this formula here in google spreadsheet:

=COUNTA(SPLIT(A1; ","))

..which also didn't work (here I simply get an error).

I guess it's about the values being non numeric? Any ideas?

3
countgoogle-sheets
You are using two different local formats. Depending on your local settings you would either use ; or , as the deliminator between the criteria. - Scott Craner
Thanks Scott - Sadly I have no clue what you just said (: It might be an explanation - what do I have to do? - pRod

3 Answers

1
votes

It is possible that you need:

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1);",";""))+1

If your Regional Settings require it. (see Scott's comment)

1
votes

This should do the trick

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

It just counts the commas and adds 1

Update
I just realized that's pretty much the same as what you had - just without using TRIM which isn't necessary. Your formula should work too.

0
votes

In Excel, use:

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

Unless there is a chance for A having no value, then you need to expand it farther:

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

Since you also tagged Google Spreadsheets, there use:

=COUNTA( SPLIT(A1, ",", TRUE))

Same applies for the possiblity of an empty field in Google Sheets.