|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 18th, 2009, 03:08 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
convert 5 characters in a cell to 6 characters by adding a zero
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 
|
|
#2
May 18th, 2009, 03:42 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
convert 5 characters in a cell to 6 characters by adding a zero
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
|
|
#3
May 18th, 2009, 04:08 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
convert 5 characters in a cell to 6 characters by adding a zer
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
|
|
#4
May 18th, 2009, 04:25 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
convert 5 characters in a cell to 6 characters by adding a zer
my error,
=if(len(a1)=6,a1&"0",""&a1) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Helenf" escreveu: 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
|
|
#5
May 18th, 2009, 04:43 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
convert 5 characters in a cell to 6 characters by adding a zer
Perfect thank you!
"Marcelo" wrote: my error, =if(len(a1)=6,a1&"0",""&a1) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Helenf" escreveu: 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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
