Comparing a list of names in 2 different list

Hello,

Please bear with me and read through all this if you can help.

I have a large list of old Product Serial Numbers which I need to check against a new large list of existing Product Serial Numbers and need to locate Numbers not duplicated, from both lists. The two lists are in separate work sheets, or could be made to be part of the same (when cut and pasted- not important immediately).

I have got a formula which would normally do that, but there is a deeper problem.

The serial numbers are 11 digits long and some have leading zeros. I know I can format it using "CUSTOM" and using 11 zeros. The problem arises when the new Product Serial Numbers are imported or cut & pasted. They seem to be formatted to CATEGORY - "Special", TYPE - "Numer PESEL" LOCALE - "Polish". Whatever that format is.
I've tried to reformat it to CUSTOM, or GENERAL or NUMBER with no decimal points, but it ALWAYS reverts back to the original format.
I've tried to use the LEFT formula command to reduce the data to 10 digits. Although this allows the format NOT to revert back to "Numer PESEL", it doesn't help as those products without leading zeros will get looked at the same as those with a different leading digit.

Unfortunately I have found another problem to one of my many "solutions".
The new list is formatted in "numer PESEL" and the old list is formatted as "text". I've tried to format both lists as "Number - no decimal places" (which kills any leading zeros). This I thought would work, but the cells that were "Text" formatted remain an 11 digit number with leading zeros. The only way that this then becomes right is when you double click an individual cell and ENTER of that cell. It does become what looks like a number without leading zeros. The problem is that I now have to do that same "double click - Enter" to every cell. The list I have is more than 4000 items long and I can't possibly do that to all the cells. (I used t think F9 would update everything but that is only if under MENU : TOOLS-OPTIONS-CALCULATION is set to manual.

I know this is long winded but I would appreciate anyones patience and help.

Thanks
XLR8R