Excel highlighting seemingly non-duplicate cells

Question

Greetings everyone,

I'm using Microsoft Office Professional Plus 2019, and I have this file that I use constantly on a daily basis.

I use Excel to store and analyze production data from a company, and as you may know, products batch numbers (LOT serial numbers) should not be repeated and used in different batches. That being said, I have a column to store that LOT serial number for each production batch; so I used conditional formatting in order to make sure I do not use the same LOT serial number in two different production batches.

For almost a year, I had practically zero problems with using conditional formatting function in order to highlight the duplicate cell values and thus alert me. However, in particularly one instance, as shown in the image, for some reason, Excel decided that 01E018 and 10E017 are the same thing and highlighted the both cells.

At first I was expecting that Excel was converting the values in these cells into numbers (i.e. Scientific formatting the cells) automatically. But that could not be the case since I have initially already anticipated such a problem and formatted the whole column as Text. Despite trying countless ideas, ranging from deleting the conditional formatting function and redoing it, changing the formatting type of the cells, using the "Clear All" command on these particular two cells and doing everything from scratch, to even inserting an apostrophe in the beginning of the text string *(which, for some weird reason, I could not delete that apostrophe after the first time and it kept reappearing by itself)*without any success.

I think the reason might still be linked to Excel treating the values within these cells as scientific numbers even though I formatted these cells as Text. You see, when there is an apostrophe in the beginning of the text string and I convert those cells' formatting into General nothing happens, and the values of 01E018 and 10E017 keeps the same without changing (still being highlighted as duplicates). However, as soon as I delete the apostrophe, Excel converts the values in both of these cells to 1,000,000,000,000,000,000 (with no commas). That somewhat makes sense, right?

I searched I found people with somewhat similar problems; but most of these people had problems when they were dealing with cells that have numbers with insignificant numbers after decimals or using characters that Excel considers as wildcards etc. However, I have yet to find someone with similar problem of mine.

Can you please help me with this?

Thanks in advance.

Answer 1

With my testing, I can confirm that Excel is converting the numbers with the E into scientific notation. Because the column is formatted as text and also using the leading single quote then it should treat them as text but for some reason this does not occur.

For the purpose of confirming that you don't have any duplicates you could select the column and use Replace to replace all of the E's with an underscore and then if all OK use replace again to replace the underscore with an E.

I suggest that you use Feedback to tell Microsoft about the problem. From within Excel select File -> Feedback -> Send a frown and then use the dialog to lodge the info relating to the problem.