Find Duplicates in a column with large number (as text)

Question

I have a SpreadSheet with a column with large number represented as text, and when I apply the duplicate operation to check ( I do not use any formula, I am using excel 2010 in-built functionality of "Conditional Formatting" -->"Highlight Cells Rule" --> "Duplicate Values") even distinct values are shown as duplicate values.

For example:

If I just have following values in a column of spread sheet:

26200008004000800000000000000001
26200008004000800000000000000002
26200008004000800000000000000003

It shows as all 3 values being duplicate. How do I fix this and check for duplicates with these large numbers in excel.

P.S: I know excel has a 15 digit limit to precision, but is there a work around or another application to find duplicates.

Answer 1

It seems that DupUnique property is converting the value to a number. I also note similar behavior with COUNTIF. Accordingly, I would suggest, in this situation, that you use the conditional format option to use a formula. The formula I would suggest (assuming that the range to check for duplicates is A2:A10, would be:

=SUMPRODUCT(--($A2=$A$2:$A$10))>1

Answer 2

I use a helper column in which I concatenate the number with a letter to make it an alphanumeric entry.

=concatenate("a",'large number cell')

or

="a"&'large number cell'

a26200008004000800000000000000001

I hope this works for you.