3

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.

Some_Guy
  • 794

1 Answers1

2

EDIT: to change from adding NUL to adding * to the criteria.

You will see similar behavior with Booleans. One way I have found to "force" COUNTIF to not do the conversions is by adding (or pre-pending) a * to the criteria argument. Eg:

=COUNTIF(A1,"<>" & A1 & "*" )

will return 0 even with the string 0001234 in A1

The "*" forces COUNTIF to treat A1 as text in the criteria argument. This will fail, of course, if A1 contains a number. So you might need to test for that

=COUNTIF(A1,"<>" &  A1 & IF(ISNUMBER(A1),"","*"))

And here is a screen shot using the data you posted in your comment:

enter image description here

Since using COUNTIF in this way is not completely bulletproof, you could also consider the EXACT function.

=N(NOT(EXACT(A2,B2)))

or even, more simply, if case sensitivity is not an issue:

=A2<>B2

will return the same results as COUNTIF with the wildcard in the criteria, and also be able exclude the possibility of the range being tested appearing within the criteria.

enter image description here

If I understand the COUNTIFS formula you posted in a comment above, the following might provide the equivalent, but I would like to see data with your expected results.

=SUMPRODUCT((A:A=A2)*(B:B<>B2))<1

Also, the formula would work much more efficiently (quickly), if you could limit the whole column references to being less than a whole column.

Ron Rosenfeld
  • 9,461