[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 730
  • Last Modified:

CountIf() comparison only looks at first 15 characters

Hi,

I am trying to use the countif() function to detect duplicate records in an Excel 2007 worksheet. The criteria I need the function to evaluate is a string of 23 characters (all numbers, no text).

The problem I am having is that the comparison used by countif() only looks at the first 15 characters in the criteria expression. For example the countif function considers the 2 strings 1234567890123451 and 1234567890123452
to be equal..

Is there some workaround for this? Or maybe a more robust function?
0
FernandoClark
Asked:
FernandoClark
1 Solution
 
mark_harris231Commented:
I do not get that result.  Can you post a sanitized sample with the formula you're using?

I used: =COUNTIF(A1:A2,"1234567890123451")
0
 
Elton PascuaCommented:
Looks like it has to do with the precision limit of Excel in large numbers. Here are some workarounds, one of which is converting to text. Another would be to append a text value like a dash at the end of the number to make it text.

http://tushar-mehta.com/misc_tutorials/project_euler/LargeNumberArithmetic.htm

Open in new window

0
 
Patrick MatthewsCommented:
If the value you are matching against is a cell reference, then you need to do something like this:

=COUNTIF(A1:A5,B1&"*")
0
 
FernandoClarkAuthor Commented:
That seems to work, although the cell must be formatted as text. Thanks, this was very helpful.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now