Count comma as zero if there is no comma in a cell

[Zaimon]

Hi guys,

Here is my formula: =LEN(W3)-LEN(SUBSTITUTE(W3, ",",""))+1

Is there is a way to change the values from 1 to 0 since there is no comma at all in the selected Cell.

Thanks,

 

[hagia_sofia]

Hello, how about:

=IF(OR(ISBLANK(W3),LEN(W3)-LEN(SUBSTITUTE(W3, ",",""))=0),0,LEN(W3)-LEN(SUBSTITUTE(W3, ",",""))+1)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[myall_blues]

Another option:

=IF(ISNUMBER(SEARCH(",",W3)),LEN(W3)-LEN(SUBSTITUTE(W3, ",",""))+1,0)

 

[felixstraube]

Would this work?:

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

or

=LEN(W3)-LEN(SUBSTITUTE(W3, ",",""))+(W3<>"")

 

[Zaimon]

Thanks for the support guys. Appreciate so much.

 

[felixstraube]

Glad we could help. Thanks for the feedback!

 

[Scott Huish]

Since you have 365, I think this would work too:
=COUNTA(TEXTSPLIT(A1,","))-1