If Function

[GN0001]

I want an if statement/ function which states find "," in a text, if there is one, then find how many of them are repeated in the cell. If there is no "," in the cell, bring back no value ("").


IF(FIND(",",$B9)<>0,(LEN($B9)-LEN(SUBSTITUTE($B9,",","")),""))


This doesn't work, when the cell doesn't have ",", the function brings back #Value. How can I take care of this matter?


Regards,

Guity

 

[Faseeh]

Hi GGGGG


Try:

=IFERROR(IF(FIND(",",$B9)<>0,LEN($B9)-LEN(SUBSTITUTE($B9,",","")),""),"")

Regards,

 

[NARAYANK991]

Hi Guity ,


Another option :


=IF(ISNUMBER(FIND(",",$B9)),LEN($B9)-LEN(SUBSTITUTE($B9,",","")),"")


Narayan

 

[Debraj]

Hi


Another option :


=IF(ISERROR(FIND(",",$B9)),"", LEN($B9)-LEN(SUBSTITUTE($B9,",","")))


Regards,

Deb

 

[SirJB7]

Hi, GGGGG!


Regarding your other post:

http://chandoo.org/forums/topic/count-the-occurence-of-a-character-in-a-cell

perhaps you could format that the cell as "#" and get displayed nothing, but consider it'll still have a zero stored.


It it's just a visual issue, the same old formula of the old post it'd be suitable; if not, choose one of those from here.


In this case you should consider further treatments of this value handling when numeric or not numeric, with the format solution you'd get rid of this... and if it's no need of further use why not stick to the previous and simple formula?


Regards!

 

[GN0001]

Hello Team,


Thank you for all your generous help:


I myself have taken care of the case with the IFERROR:

(IFERROR(IF(FIND(",",C13)>0,(LEN(C13)-LEN(SUBSTITUTE(C13,",",""))+1),""),""))

But I think the second and third version solutions are better solutions.


Also, in response to SirJB7 asking why I am not using only (LEN(C13)-LEN(SUBSTITUTE(C13,",","") is that I have to add number 1 to the result. If this

(LEN(C13)-LEN(SUBSTITUTE(C13,",","") brings #Value! then I can't add 1 to #Value!.


Regards,

GGGGG

 

[SirJB7]

Hi, GGGGG!

I entered manually random values from keyboard and pasting from other sources and I can't get an error of #Value!. Would you please post your cell entry? Thank you. With values without commas I get 0, not the referred error.

Regards!