convert 5 characters in a cell to 6 characters by adding a zero

H

Helenf

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
 
M

Marcelo

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:
 
M

Marcelo

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:
 
H

Helenf

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?
 
H

Helenf

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?
 
M

Marcelo

my error,

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


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



"Helenf" escreveu:
 
M

Marcelo

my error,

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


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



"Helenf" escreveu:
 
H

Helenf

Perfect thank you!

Marcelo said:
my error,

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


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



"Helenf" escreveu:
Click to expand...
 
H

Helenf

Perfect thank you!

Marcelo said:
my error,

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


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



"Helenf" escreveu:
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select cells according to row number in different sheet 6
Excel If a Cell Contains a Certain Text (From Another Table) 0
how do I add a zero in front of a bunch of 4 digit numbers? 7
How do I force a leading zero character eg 07817 3
Extracting last 5 characters from a cell 2
Replacing specific characters 3
linking sheets in the workbook and getting rid of the ZERO 3
how to display zero at the left hand of a code in a cell say 045 3

Top