different long numbers formatted as text are considered duplicates

I'm not sure why Excel would do this, but I expect you are up against the 15 digit limit for numbers. For whatever reason, during the test for duplicates, these "numbers stored as text" are getting converted to actual numbers, and the 16th digit gets lost. Since the sample values you give are identical to 15 digits, they get flagged as duplicates.

I'm not sure what a suitable workaround would be. My first thought is to add a character to the beginning or end of each number string so that Excel cannot convert the text string to a number. 3081281170122602a and 3081281170122601a are correctly seen as not duplicates.

Maybe given a little more time, someone will suggest another possible solution.

I think that I figured out a way to get around the 15 digit limitation. I created a helper column F and a VLOOKKUP TABLE that converted the last digit to an alpha character and then entered this formula in F3 and copied down:

Formula:

Please Login or Register  to view this content.

The conditional formatting was created by selecting column C and using this formula

Formula:

Please Login or Register  to view this content.

This does not change the original data in any way.

The helper column can be hidden.

Good!

Thanks for the feedback.