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:
 
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:
 
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 3
External cell reference between two excel worksheets both in One Drive 0
Formula to validate characters within a string of data in aparticular cell 1
how do I add a zero in front of a bunch of 4 digit numbers? 7
Autocopy the first cell with more than 30 characters to a different sheet? 1
Number of characters to be displayed in a cell 10
Counting by numbers of Characters in a cell 5
Convert column of 4-5 digit whole numbers to 6? 3

Top