Conditional formatting of duplicate numbers with leading zeros

[KBBersch]

I have a spreadsheet to keep track of lab equipment. I have the Serial Number column set to find duplicates and highlight them with a red background. The entire column has the cells formatted as TEXT. I just came across a problem with two separate items that show as duplicates. For the first the serial number is 2, for the second the serial number is 002. The two are highlighted as duplicates of each other. I had expected that, with the cells formatted as Text, they would appear as different values but that is not the case. Any ideas on how to work around this shortcoming in Excel?

There was a thread on this a couple of years ago but it kind of petered out with no solution.

Thanks.

 

[Robby19]

The reason is because excel looks at the Integer of 001, 01, and 1 all as 1 which is the same number. If it is possible, you could create a helper column using CONCATENATE and conditionally format the duplicates there.

In Column B use

=CONCATENATE(A2,".",C2)

And copy down. You could then take your Conditional Formatting and base it off Column B rather than A.

For example;

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Part Number
[/TD]
[TD]Part ID
[/TD]
[TD]Part Name
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]001
[/TD]
[TD]001.ABC
[/TD]
[TD]ABC
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]01
[/TD]
[TD]01.BCD
[/TD]
[TD]BCD
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1
[/TD]
[TD]1.CDE
[/TD]
[TD]CDE
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]2
[/TD]
[TD]2.DEF
[/TD]
[TD]DEF
[/TD]
[/TR]
</tbody>[/TABLE]