13

Simple test case:

  • Create new workbook
  • Format column A as "Text"
  • Enter the following into cells A1 and A2:
1234567800000639
1234567800000630
  • Select Column A, and in the "Conditional Formatting" submenu, select "Highlight Cells Rule" > "Duplicate Values".

Excel is highlighting these as duplicates, even though they are different. Screenshot of Excel

I suspect Excel is treating them as numbers and it becomes a rounding error, but I have specifically set to Text format and visually see different values, so I'm not sure how to tell Excel to treat it as Text.

Improve this question
2
  • Excel handle the values as numbers and replace last four numbers with zeroes. This the reason of this problem.
    – Lee
    Commented Jul 15, 2021 at 7:39
  • 2
    It works in Google Sheets!
    – trinalbadger587
    Commented Jul 15, 2021 at 16:50

1 Answer 1

Reset to default
12

Excel has a 15-digit limit to numerical precision, and as you've worked out your numbers have 16 so Excel is converting to a number and can't tell they're not duplicates (despite the fact you've marked them as text and put ' at the start of the numbers to signify a string).

One workaround is to concatenate a letter onto the column of strings you are testing for duplicates. As you can see here, by adding "a" on the front of your string Excel treats it as a real string.

enter image description here

Improve this answer
3
  • See also: stackoverflow.com/questions/32960322/…
    – Andi Mohr
    Commented Jul 14, 2021 at 15:52
  • 19
    "despite ... signify a string" and still showing as a duplicate qualifies this as a bug IMO
    – dlatikay
    Commented Jul 15, 2021 at 7:15
  • 2
    Agreed! It's a bug that's been around for a while
    – Andi Mohr
    Commented Jul 15, 2021 at 12:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .