I have some product codes, some of which are numbers with leading zeroes, stored in a spreadsheet.
I'm doing some operations that rely on a COUNTIF not equal to function (details in comments but not relevant to the question). Countif seems to break when dealing with strings containing numbers with leading zeroes.
For a simple demonstration
For example if A1 contains "foo" then:
=COUNTIF(A1,"<>"&A1)
returns 0. That is to say, there are no cells in A1 that are not equal to A1 (of course).
But if A1 contains "0001234" as text, then it returns 1, i.e. A1 IS NOT EQUAL TO A1
Further info: =countif(A1:"<>"&"1234")
returns 0, so the problem looks to be that excel autmatically converts the string "0001234" to the number 1234, but only in the criteria and not in the range and then finds "0001234"=/=1234.
WORKAROUND: create a helper column with a junk text character appended to all codes. I currently have added a helper column containing ="@"&X1 to all my lists, but it's not an elegant solution.
=Countif(A1:A100,"<>"&A1)
?