my COUNTIF formula gives result 1 instead of zero

[Sandy_Patel]

Hello
I have excel sheet in which cell A1 is empty. Cell A2 contains text as Alice . Cell A3 contains formula as
=COUNTIF(A2,"*"&A1&"*")

Result of this formula is 1

I put star before and after A1 in formula of COUNTIF means excel match anything from cell A1 with cell A2.
So what does excel match similar between cell A1 and cell A2 so formula gives result 1.
There is not anything in cell A1 so result of this formula should be zero
Could any one explain why I get result 1 ? I think, above formula should give result zero (0) because
If I remove two star in above formula then result is zero so I understand this.

Find sheet to have a look .
Thanks

 

[Hui]

Sandy

=COUNTIF(A2,"*"&A1&"*")

Says count the number of entries in range A2 which match the Value in A1 with anything before or after it
So If A1 is Blank, A2 matches the Blank as it has Alice before and after the blank

If you put a A in A1 A2 matches as it has "lice" after the A
If you put a D in A1 it returns 0 as there is nothing that matches *D* in A2

 

[Hui]

Also note that a space in A1 isn't the same as a Blank and will return 0

 

[Sandy_Patel]

Thanks Hui for quick reply

Got idea