=countif says "0001234" is not equal to "0001234" How to force countif to treat strings as text not numbers?

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.

• shouldn't the formula be something like `=Countif(A1:A100,"<>"&A1)`? Aug 30, 2016 at 16:21
• Hi there ray, always good to see you on a question. The actual function I was using is =COUNTIFS(A:A,A1,B:B,"<>"&B1)<1 Which returns FALSE if there is a record with the same identifier in A but a different value in B. B should be unique to A (but not vice versa) in this data, I'm doing some error checking. (i.e. product 2345 could have attribute 001. There may be other products with atribute 001 too in the list, but 2345 should never have both attribute 001 and 002) Aug 31, 2016 at 11:01
• I didn't include this in the question as I thought it might obfuscate the actual problem I'm facing of countif's behaviour. The task I'm performing may have a more elegant solution than mine, but at the end of the day my formula does do what it should (except with codes with leading 0s). I have a workaround, but it seems worthwhile to have this problem "on the record" as it were. Aug 31, 2016 at 11:01
• Sometimes VBa is a better choice, but I'm assuming that's not an option for you?
– Dave
Sep 1, 2016 at 11:42

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:

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.

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.

• This seems to just always return 0, even if things aren't equal. See attached image imgur.com/a/6VSYF Aug 31, 2016 at 11:16
• @Some_Guy You are correct, of course. Don't know why I thought it was working. Will Edit to the correct solution. Aug 31, 2016 at 11:27
• This is better, however it relies on a wildcard being used. With where you can guarantee one item will contain another item, but if I had one code 0002345 and another code 0002345A this would cause a problem. Definitely an improvement on the previous situation though. Sep 1, 2016 at 9:51
• @Some_Guy Yes, that will be a limitation. Whether it is fatal depends on your database -- only you can judge that. If you are not wedded to using `COUNTIF`, there are other solutions. I will post one. Sep 1, 2016 at 10:09