Home |
Search |
Today's Posts |
#1
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
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
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
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
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert Leading Characters If String Is Only 7 Characters | Excel Discussion (Misc queries) | |||
Limiting Characters in a cell and adding spaces to fill the remain | Excel Discussion (Misc queries) | |||
Replacing last 3 characters and adding them to front | Excel Discussion (Misc queries) | |||
In Excel find characters when multiple characters exist w/i a cel | Excel Worksheet Functions | |||
Adding characters | Excel Worksheet Functions |