Thank you Marcelo

However this returns two zeros instead of just the one creating a 7
character string. How do I change the formula so only 1 zero appears?

"Marcelo" wrote:

to convert the number to text adding the 6th 0 i suggest yuo to use:

=if(len(a1)=6,a1&0&"0",""&a1)


hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Helenf" escreveu:

Thank you in advance

I have 2 different spreadsheets of about 18000 lines each of products. The
item codes on sheet one muct match the ones on sheet 2. However somewhere the
supplier returned the one sheet with a different formatting to the other. The
codes are in the format of xxx.xxx where x is a number. The codes that have a
zero at the end were changed to xxx.xx and therefor our system can not match
them

Is there a way to add the zero back to the 1000+ items that are missing them
but not a zero to all the items? The one sheet is number format and the other
is text format.
Once again thank you in advance