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.

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

1 Answer 1

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

3

You must log in to answer this question.

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