Remove the zero

[John Caines]

Hi All.
I have a formula here that generates a number depending on the value in cell E2 which is;

=IF(ISBLANK(E2),,COUNTIF(B$7:$B13259, "*"&E2&"*")&" matches were found")

Trouble is, if cell E2 is empty, I see a zero (0) in cell H2 where the formula is
Does anyone know if there is anyway to change the formula so it won't show a zero if cell E2 is empty?

PS, I know I can go into excel File/ options/advance and turn off 'Show a zero in cells that have a zero value' but I really don't want to uncheck this as it affects other cells in the sheet.

Hope there might be a way to tweak this formula so it doesn't show the zeros.

Many thanks in advance.
Hope someone can fathom it out, I just don't know the answer

Best regards
John C

 

[Rick Rothstein]

You have to specifically use "" instead of having nothing between those two adjacent commas...

=IF(ISBLANK(E2),"",COUNTIF(B$7:$B13259, "*"&E2&"*")&" matches were found")

 

[John Caines]

Hi Rick!
Many thanks for your reply, yes ,,, that's it! Dame, I should have got that 1,,

Funny you replied as I was using a great formula by you yesterday, as I needed to go back to it, it was your 'Get Field from Delimited Text String' that's really really helpful for me.

Many thanks again Rick for your swift reply, very much appreciated!
Bank Holiday here in the UK, not sure if it's a holiday over in the US, but if it is, enjoy your Bank Holiday!

Cheers Rick
Best regards
John Caines