Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,047
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,047
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert Leading Characters If String Is Only 7 Characters Paperback Writer Excel Discussion (Misc queries) 2 April 21st 09 09:07 PM
Limiting Characters in a cell and adding spaces to fill the remain Melissa Excel Discussion (Misc queries) 5 January 11th 08 02:35 AM
Replacing last 3 characters and adding them to front [email protected] Excel Discussion (Misc queries) 4 December 18th 06 09:29 PM
In Excel find characters when multiple characters exist w/i a cel teacher-deburg Excel Worksheet Functions 1 December 5th 05 11:22 PM
Adding characters Amy Johnson Excel Worksheet Functions 3 April 28th 05 07:41 PM


All times are GMT +1. The time now is 10:38 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"