Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I stop Excel from dropping leading zeros?
I am using Excel 2003. Excel is dropping the leading zeros from 12 digit UPC
numbers I paste into a spreadsheet. I know how to restore the leading zeros using the special cell formatting option "000000000000" to make the number 12 digits, this pads the beginning of the number with leading zero's to fill it out to 12 digits. However, I also need to drop the last digit (the check digit) from the UPC numbers that I am pasting. When I use a formula (in this case =LEFT(F2,LEN(F2)-1); I apply this formula to the entire column to the right of the column with the UPC numbers [column F] and it drops the last digit) to drop the last digit from the UPC, the numbers that are generated unfortunately seem to have dropped the lead zeros again. And once I have formatted this column with the formula to generate the UPC minus the check digit, I don't seem to be able to apply a custom number format to the cells to make them 11 digits, thus restoring the lost leading zeros. Is there any way to make Excel quit dropping leading zeros? Or is there any way to both have a formula applied to a column AND a custom number format? I am totally stuck here. Thanks for any help |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I stop Excel from dropping leading zeros?
Many people have asked how they can retain leading zeros and there are a
variety of solutions offered. However, I haven't seen my situation and I haven't found a solution. I need to copy and paste into Excel many tables of accounting data. The tables have multiple columns. The values may contain valid leading zeros and the values are not fixed width. There are thousands of records so entering a leading ' is not practical. I have tried several solutions and none work. Specifically, I have tried preformating the cells in a new spreadsheet as text before I paste. The leading zeros are dropped. I have tried paste special/text. All the columns from the source table end up in column A. Is there a way to disable auto format? Is there anything else I can try? Thanks, David "David Biddulph" wrote: The reason that you can't see any response to a custom format after you've used the LEFT function is that custom formats don't work on text strings, and LEFT returns a text result. If you want to convert the text value to a number use --LEFT(...) The reason that your LEFT function doesn't see your leading zeros is that the custom formatting affects only the display, not the underlying content, so when the formula refers to F2 it sees the number in the cell, not your formatted display version. If you want the LEFT function to see the formatted version, replace the F2 references by TEXT(F2,"000000000000"). -- David Biddulph "Carl Nash" <Carl wrote in message ... I am using Excel 2003. Excel is dropping the leading zeros from 12 digit UPC numbers I paste into a spreadsheet. I know how to restore the leading zeros using the special cell formatting option "000000000000" to make the number 12 digits, this pads the beginning of the number with leading zero's to fill it out to 12 digits. However, I also need to drop the last digit (the check digit) from the UPC numbers that I am pasting. When I use a formula (in this case =LEFT(F2,LEN(F2)-1); I apply this formula to the entire column to the right of the column with the UPC numbers [column F] and it drops the last digit) to drop the last digit from the UPC, the numbers that are generated unfortunately seem to have dropped the lead zeros again. And once I have formatted this column with the formula to generate the UPC minus the check digit, I don't seem to be able to apply a custom number format to the cells to make them 11 digits, thus restoring the lost leading zeros. Is there any way to make Excel quit dropping leading zeros? Or is there any way to both have a formula applied to a column AND a custom number format? I am totally stuck here. Thanks for any help |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I stop Excel from dropping leading zeros?
Copy and paste into a text editor like NotePad.
Save as a *.txt file. Open that file in Excel and the Text Import Wizard will allow you to designate the data as Text. Gord Dibben MS Excel MVP On Wed, 22 Apr 2009 08:18:09 -0700, 4110 wrote: Many people have asked how they can retain leading zeros and there are a variety of solutions offered. However, I haven't seen my situation and I haven't found a solution. I need to copy and paste into Excel many tables of accounting data. The tables have multiple columns. The values may contain valid leading zeros and the values are not fixed width. There are thousands of records so entering a leading ' is not practical. I have tried several solutions and none work. Specifically, I have tried preformating the cells in a new spreadsheet as text before I paste. The leading zeros are dropped. I have tried paste special/text. All the columns from the source table end up in column A. Is there a way to disable auto format? Is there anything else I can try? Thanks, David "David Biddulph" wrote: The reason that you can't see any response to a custom format after you've used the LEFT function is that custom formats don't work on text strings, and LEFT returns a text result. If you want to convert the text value to a number use --LEFT(...) The reason that your LEFT function doesn't see your leading zeros is that the custom formatting affects only the display, not the underlying content, so when the formula refers to F2 it sees the number in the cell, not your formatted display version. If you want the LEFT function to see the formatted version, replace the F2 references by TEXT(F2,"000000000000"). -- David Biddulph "Carl Nash" <Carl wrote in message ... I am using Excel 2003. Excel is dropping the leading zeros from 12 digit UPC numbers I paste into a spreadsheet. I know how to restore the leading zeros using the special cell formatting option "000000000000" to make the number 12 digits, this pads the beginning of the number with leading zero's to fill it out to 12 digits. However, I also need to drop the last digit (the check digit) from the UPC numbers that I am pasting. When I use a formula (in this case =LEFT(F2,LEN(F2)-1); I apply this formula to the entire column to the right of the column with the UPC numbers [column F] and it drops the last digit) to drop the last digit from the UPC, the numbers that are generated unfortunately seem to have dropped the lead zeros again. And once I have formatted this column with the formula to generate the UPC minus the check digit, I don't seem to be able to apply a custom number format to the cells to make them 11 digits, thus restoring the lost leading zeros. Is there any way to make Excel quit dropping leading zeros? Or is there any way to both have a formula applied to a column AND a custom number format? I am totally stuck here. Thanks for any help |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I stop Excel from dropping leading zeros?
Thank you for your reply. Per your suggestion, I pasted a table into NotePad
and saved it as a .txt file. Then I used Data/Import External Data/Import Data to import into Excel. It imported, but with problems. I couldn't find a delimiter that worked. If I used spaces I ended up with far more than the three actual columns. With any other delimiter I got everything in one column. Note: If I paste straight from the source it properly becomes three columns in Excel (but without the leading zeros). Something about going through Notepad removes what Excel sees to create three columns from the original source. David "Gord Dibben" wrote: Copy and paste into a text editor like NotePad. Save as a *.txt file. Open that file in Excel and the Text Import Wizard will allow you to designate the data as Text. Gord Dibben MS Excel MVP On Wed, 22 Apr 2009 08:18:09 -0700, 4110 wrote: Many people have asked how they can retain leading zeros and there are a variety of solutions offered. However, I haven't seen my situation and I haven't found a solution. I need to copy and paste into Excel many tables of accounting data. The tables have multiple columns. The values may contain valid leading zeros and the values are not fixed width. There are thousands of records so entering a leading ' is not practical. I have tried several solutions and none work. Specifically, I have tried preformating the cells in a new spreadsheet as text before I paste. The leading zeros are dropped. I have tried paste special/text. All the columns from the source table end up in column A. Is there a way to disable auto format? Is there anything else I can try? Thanks, David "David Biddulph" wrote: The reason that you can't see any response to a custom format after you've used the LEFT function is that custom formats don't work on text strings, and LEFT returns a text result. If you want to convert the text value to a number use --LEFT(...) The reason that your LEFT function doesn't see your leading zeros is that the custom formatting affects only the display, not the underlying content, so when the formula refers to F2 it sees the number in the cell, not your formatted display version. If you want the LEFT function to see the formatted version, replace the F2 references by TEXT(F2,"000000000000"). -- David Biddulph "Carl Nash" <Carl wrote in message ... I am using Excel 2003. Excel is dropping the leading zeros from 12 digit UPC numbers I paste into a spreadsheet. I know how to restore the leading zeros using the special cell formatting option "000000000000" to make the number 12 digits, this pads the beginning of the number with leading zero's to fill it out to 12 digits. However, I also need to drop the last digit (the check digit) from the UPC numbers that I am pasting. When I use a formula (in this case =LEFT(F2,LEN(F2)-1); I apply this formula to the entire column to the right of the column with the UPC numbers [column F] and it drops the last digit) to drop the last digit from the UPC, the numbers that are generated unfortunately seem to have dropped the lead zeros again. And once I have formatted this column with the formula to generate the UPC minus the check digit, I don't seem to be able to apply a custom number format to the cells to make them 11 digits, thus restoring the lost leading zeros. Is there any way to make Excel quit dropping leading zeros? Or is there any way to both have a formula applied to a column AND a custom number format? I am totally stuck here. Thanks for any help |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I stop Excel from dropping leading zeros?
My suggestion was to open the *.txt file directly.
I did not suggest DataImport External Data. Gord On Wed, 22 Apr 2009 08:52:02 -0700, 4110 wrote: Thank you for your reply. Per your suggestion, I pasted a table into NotePad and saved it as a .txt file. Then I used Data/Import External Data/Import Data to import into Excel. It imported, but with problems. I couldn't find a delimiter that worked. If I used spaces I ended up with far more than the three actual columns. With any other delimiter I got everything in one column. Note: If I paste straight from the source it properly becomes three columns in Excel (but without the leading zeros). Something about going through Notepad removes what Excel sees to create three columns from the original source. David "Gord Dibben" wrote: Copy and paste into a text editor like NotePad. Save as a *.txt file. Open that file in Excel and the Text Import Wizard will allow you to designate the data as Text. Gord Dibben MS Excel MVP On Wed, 22 Apr 2009 08:18:09 -0700, 4110 wrote: Many people have asked how they can retain leading zeros and there are a variety of solutions offered. However, I haven't seen my situation and I haven't found a solution. I need to copy and paste into Excel many tables of accounting data. The tables have multiple columns. The values may contain valid leading zeros and the values are not fixed width. There are thousands of records so entering a leading ' is not practical. I have tried several solutions and none work. Specifically, I have tried preformating the cells in a new spreadsheet as text before I paste. The leading zeros are dropped. I have tried paste special/text. All the columns from the source table end up in column A. Is there a way to disable auto format? Is there anything else I can try? Thanks, David "David Biddulph" wrote: The reason that you can't see any response to a custom format after you've used the LEFT function is that custom formats don't work on text strings, and LEFT returns a text result. If you want to convert the text value to a number use --LEFT(...) The reason that your LEFT function doesn't see your leading zeros is that the custom formatting affects only the display, not the underlying content, so when the formula refers to F2 it sees the number in the cell, not your formatted display version. If you want the LEFT function to see the formatted version, replace the F2 references by TEXT(F2,"000000000000"). -- David Biddulph "Carl Nash" <Carl wrote in message ... I am using Excel 2003. Excel is dropping the leading zeros from 12 digit UPC numbers I paste into a spreadsheet. I know how to restore the leading zeros using the special cell formatting option "000000000000" to make the number 12 digits, this pads the beginning of the number with leading zero's to fill it out to 12 digits. However, I also need to drop the last digit (the check digit) from the UPC numbers that I am pasting. When I use a formula (in this case =LEFT(F2,LEN(F2)-1); I apply this formula to the entire column to the right of the column with the UPC numbers [column F] and it drops the last digit) to drop the last digit from the UPC, the numbers that are generated unfortunately seem to have dropped the lead zeros again. And once I have formatted this column with the formula to generate the UPC minus the check digit, I don't seem to be able to apply a custom number format to the cells to make them 11 digits, thus restoring the lost leading zeros. Is there any way to make Excel quit dropping leading zeros? Or is there any way to both have a formula applied to a column AND a custom number format? I am totally stuck here. Thanks for any help |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I stop Excel from dropping leading zeros?
Thank you for the correction. This time I opened Windows Explorer and right
clicked on the .txt file I had saved in Wordpad. I then clicked 'Open With' and selected Excel from the list. Excel opened and all three columns from the source were in a single column. With the data now in Excel I used the Data/Text To Column Wizard to try to parse the data. It responded like the Import Data Wizard and didn't see the column delimitors that Excel can see if I paste from the source directly into Excel. David. "Gord Dibben" wrote: My suggestion was to open the *.txt file directly. I did not suggest DataImport External Data. Gord On Wed, 22 Apr 2009 08:52:02 -0700, 4110 wrote: Thank you for your reply. Per your suggestion, I pasted a table into NotePad and saved it as a .txt file. Then I used Data/Import External Data/Import Data to import into Excel. It imported, but with problems. I couldn't find a delimiter that worked. If I used spaces I ended up with far more than the three actual columns. With any other delimiter I got everything in one column. Note: If I paste straight from the source it properly becomes three columns in Excel (but without the leading zeros). Something about going through Notepad removes what Excel sees to create three columns from the original source. David "Gord Dibben" wrote: Copy and paste into a text editor like NotePad. Save as a *.txt file. Open that file in Excel and the Text Import Wizard will allow you to designate the data as Text. Gord Dibben MS Excel MVP On Wed, 22 Apr 2009 08:18:09 -0700, 4110 wrote: Many people have asked how they can retain leading zeros and there are a variety of solutions offered. However, I haven't seen my situation and I haven't found a solution. I need to copy and paste into Excel many tables of accounting data. The tables have multiple columns. The values may contain valid leading zeros and the values are not fixed width. There are thousands of records so entering a leading ' is not practical. I have tried several solutions and none work. Specifically, I have tried preformating the cells in a new spreadsheet as text before I paste. The leading zeros are dropped. I have tried paste special/text. All the columns from the source table end up in column A. Is there a way to disable auto format? Is there anything else I can try? Thanks, David "David Biddulph" wrote: The reason that you can't see any response to a custom format after you've used the LEFT function is that custom formats don't work on text strings, and LEFT returns a text result. If you want to convert the text value to a number use --LEFT(...) The reason that your LEFT function doesn't see your leading zeros is that the custom formatting affects only the display, not the underlying content, so when the formula refers to F2 it sees the number in the cell, not your formatted display version. If you want the LEFT function to see the formatted version, replace the F2 references by TEXT(F2,"000000000000"). -- David Biddulph "Carl Nash" <Carl wrote in message ... I am using Excel 2003. Excel is dropping the leading zeros from 12 digit UPC numbers I paste into a spreadsheet. I know how to restore the leading zeros using the special cell formatting option "000000000000" to make the number 12 digits, this pads the beginning of the number with leading zero's to fill it out to 12 digits. However, I also need to drop the last digit (the check digit) from the UPC numbers that I am pasting. When I use a formula (in this case =LEFT(F2,LEN(F2)-1); I apply this formula to the entire column to the right of the column with the UPC numbers [column F] and it drops the last digit) to drop the last digit from the UPC, the numbers that are generated unfortunately seem to have dropped the lead zeros again. And once I have formatted this column with the formula to generate the UPC minus the check digit, I don't seem to be able to apply a custom number format to the cells to make them 11 digits, thus restoring the lost leading zeros. Is there any way to make Excel quit dropping leading zeros? Or is there any way to both have a formula applied to a column AND a custom number format? I am totally stuck here. Thanks for any help |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I stop Excel from dropping leading zeros?
Can you post the *.txt file you created to one of these upload sites?
http://www.freefilehosting.net/ http://savefile.com/ After uploading, post the URL. I will have a look at the file and open it in Excel. Gord On Wed, 22 Apr 2009 10:27:46 -0700, 4110 wrote: Thank you for the correction. This time I opened Windows Explorer and right clicked on the .txt file I had saved in Wordpad. I then clicked 'Open With' and selected Excel from the list. Excel opened and all three columns from the source were in a single column. With the data now in Excel I used the Data/Text To Column Wizard to try to parse the data. It responded like the Import Data Wizard and didn't see the column delimitors that Excel can see if I paste from the source directly into Excel. David. "Gord Dibben" wrote: My suggestion was to open the *.txt file directly. I did not suggest DataImport External Data. Gord On Wed, 22 Apr 2009 08:52:02 -0700, 4110 wrote: Thank you for your reply. Per your suggestion, I pasted a table into NotePad and saved it as a .txt file. Then I used Data/Import External Data/Import Data to import into Excel. It imported, but with problems. I couldn't find a delimiter that worked. If I used spaces I ended up with far more than the three actual columns. With any other delimiter I got everything in one column. Note: If I paste straight from the source it properly becomes three columns in Excel (but without the leading zeros). Something about going through Notepad removes what Excel sees to create three columns from the original source. David "Gord Dibben" wrote: Copy and paste into a text editor like NotePad. Save as a *.txt file. Open that file in Excel and the Text Import Wizard will allow you to designate the data as Text. Gord Dibben MS Excel MVP On Wed, 22 Apr 2009 08:18:09 -0700, 4110 wrote: Many people have asked how they can retain leading zeros and there are a variety of solutions offered. However, I haven't seen my situation and I haven't found a solution. I need to copy and paste into Excel many tables of accounting data. The tables have multiple columns. The values may contain valid leading zeros and the values are not fixed width. There are thousands of records so entering a leading ' is not practical. I have tried several solutions and none work. Specifically, I have tried preformating the cells in a new spreadsheet as text before I paste. The leading zeros are dropped. I have tried paste special/text. All the columns from the source table end up in column A. Is there a way to disable auto format? Is there anything else I can try? Thanks, David "David Biddulph" wrote: The reason that you can't see any response to a custom format after you've used the LEFT function is that custom formats don't work on text strings, and LEFT returns a text result. If you want to convert the text value to a number use --LEFT(...) The reason that your LEFT function doesn't see your leading zeros is that the custom formatting affects only the display, not the underlying content, so when the formula refers to F2 it sees the number in the cell, not your formatted display version. If you want the LEFT function to see the formatted version, replace the F2 references by TEXT(F2,"000000000000"). -- David Biddulph "Carl Nash" <Carl wrote in message ... I am using Excel 2003. Excel is dropping the leading zeros from 12 digit UPC numbers I paste into a spreadsheet. I know how to restore the leading zeros using the special cell formatting option "000000000000" to make the number 12 digits, this pads the beginning of the number with leading zero's to fill it out to 12 digits. However, I also need to drop the last digit (the check digit) from the UPC numbers that I am pasting. When I use a formula (in this case =LEFT(F2,LEN(F2)-1); I apply this formula to the entire column to the right of the column with the UPC numbers [column F] and it drops the last digit) to drop the last digit from the UPC, the numbers that are generated unfortunately seem to have dropped the lead zeros again. And once I have formatted this column with the formula to generate the UPC minus the check digit, I don't seem to be able to apply a custom number format to the cells to make them 11 digits, thus restoring the lost leading zeros. Is there any way to make Excel quit dropping leading zeros? Or is there any way to both have a formula applied to a column AND a custom number format? I am totally stuck here. Thanks for any help |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I stop Excel from dropping leading zeros?
I have modified the original .txt file by changing actual values and reducing
the number of rows to about a dozen. I uploaded the sample file to the Savefile host location. I don't know how to get the URL but the file name is "Excel Leading Zero Import" and the number is 2083021. "Gord Dibben" wrote: Can you post the *.txt file you created to one of these upload sites? http://www.freefilehosting.net/ http://savefile.com/ After uploading, post the URL. I will have a look at the file and open it in Excel. Gord On Wed, 22 Apr 2009 10:27:46 -0700, 4110 wrote: Thank you for the correction. This time I opened Windows Explorer and right clicked on the .txt file I had saved in Wordpad. I then clicked 'Open With' and selected Excel from the list. Excel opened and all three columns from the source were in a single column. With the data now in Excel I used the Data/Text To Column Wizard to try to parse the data. It responded like the Import Data Wizard and didn't see the column delimitors that Excel can see if I paste from the source directly into Excel. David. "Gord Dibben" wrote: My suggestion was to open the *.txt file directly. I did not suggest DataImport External Data. Gord On Wed, 22 Apr 2009 08:52:02 -0700, 4110 wrote: Thank you for your reply. Per your suggestion, I pasted a table into NotePad and saved it as a .txt file. Then I used Data/Import External Data/Import Data to import into Excel. It imported, but with problems. I couldn't find a delimiter that worked. If I used spaces I ended up with far more than the three actual columns. With any other delimiter I got everything in one column. Note: If I paste straight from the source it properly becomes three columns in Excel (but without the leading zeros). Something about going through Notepad removes what Excel sees to create three columns from the original source. David "Gord Dibben" wrote: Copy and paste into a text editor like NotePad. Save as a *.txt file. Open that file in Excel and the Text Import Wizard will allow you to designate the data as Text. Gord Dibben MS Excel MVP On Wed, 22 Apr 2009 08:18:09 -0700, 4110 wrote: Many people have asked how they can retain leading zeros and there are a variety of solutions offered. However, I haven't seen my situation and I haven't found a solution. I need to copy and paste into Excel many tables of accounting data. The tables have multiple columns. The values may contain valid leading zeros and the values are not fixed width. There are thousands of records so entering a leading ' is not practical. I have tried several solutions and none work. Specifically, I have tried preformating the cells in a new spreadsheet as text before I paste. The leading zeros are dropped. I have tried paste special/text. All the columns from the source table end up in column A. Is there a way to disable auto format? Is there anything else I can try? Thanks, David "David Biddulph" wrote: The reason that you can't see any response to a custom format after you've used the LEFT function is that custom formats don't work on text strings, and LEFT returns a text result. If you want to convert the text value to a number use --LEFT(...) The reason that your LEFT function doesn't see your leading zeros is that the custom formatting affects only the display, not the underlying content, so when the formula refers to F2 it sees the number in the cell, not your formatted display version. If you want the LEFT function to see the formatted version, replace the F2 references by TEXT(F2,"000000000000"). -- David Biddulph "Carl Nash" <Carl wrote in message ... I am using Excel 2003. Excel is dropping the leading zeros from 12 digit UPC numbers I paste into a spreadsheet. I know how to restore the leading zeros using the special cell formatting option "000000000000" to make the number 12 digits, this pads the beginning of the number with leading zero's to fill it out to 12 digits. However, I also need to drop the last digit (the check digit) from the UPC numbers that I am pasting. When I use a formula (in this case =LEFT(F2,LEN(F2)-1); I apply this formula to the entire column to the right of the column with the UPC numbers [column F] and it drops the last digit) to drop the last digit from the UPC, the numbers that are generated unfortunately seem to have dropped the lead zeros again. And once I have formatted this column with the formula to generate the UPC minus the check digit, I don't seem to be able to apply a custom number format to the cells to make them 11 digits, thus restoring the lost leading zeros. Is there any way to make Excel quit dropping leading zeros? Or is there any way to both have a formula applied to a column AND a custom number format? I am totally stuck here. Thanks for any help |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I stop Excel from dropping leading zeros?
Not having a URL cannot assist me in downloading the file.
Send the *.txt file to my email. Change the AT and DOT in my address gorddibbATshawDOTca Gord On Wed, 22 Apr 2009 11:22:01 -0700, 4110 wrote: I have modified the original .txt file by changing actual values and reducing the number of rows to about a dozen. I uploaded the sample file to the Savefile host location. I don't know how to get the URL but the file name is "Excel Leading Zero Import" and the number is 2083021. "Gord Dibben" wrote: Can you post the *.txt file you created to one of these upload sites? http://www.freefilehosting.net/ http://savefile.com/ After uploading, post the URL. I will have a look at the file and open it in Excel. Gord On Wed, 22 Apr 2009 10:27:46 -0700, 4110 wrote: Thank you for the correction. This time I opened Windows Explorer and right clicked on the .txt file I had saved in Wordpad. I then clicked 'Open With' and selected Excel from the list. Excel opened and all three columns from the source were in a single column. With the data now in Excel I used the Data/Text To Column Wizard to try to parse the data. It responded like the Import Data Wizard and didn't see the column delimitors that Excel can see if I paste from the source directly into Excel. David. "Gord Dibben" wrote: My suggestion was to open the *.txt file directly. I did not suggest DataImport External Data. Gord On Wed, 22 Apr 2009 08:52:02 -0700, 4110 wrote: Thank you for your reply. Per your suggestion, I pasted a table into NotePad and saved it as a .txt file. Then I used Data/Import External Data/Import Data to import into Excel. It imported, but with problems. I couldn't find a delimiter that worked. If I used spaces I ended up with far more than the three actual columns. With any other delimiter I got everything in one column. Note: If I paste straight from the source it properly becomes three columns in Excel (but without the leading zeros). Something about going through Notepad removes what Excel sees to create three columns from the original source. David "Gord Dibben" wrote: Copy and paste into a text editor like NotePad. Save as a *.txt file. Open that file in Excel and the Text Import Wizard will allow you to designate the data as Text. Gord Dibben MS Excel MVP On Wed, 22 Apr 2009 08:18:09 -0700, 4110 wrote: Many people have asked how they can retain leading zeros and there are a variety of solutions offered. However, I haven't seen my situation and I haven't found a solution. I need to copy and paste into Excel many tables of accounting data. The tables have multiple columns. The values may contain valid leading zeros and the values are not fixed width. There are thousands of records so entering a leading ' is not practical. I have tried several solutions and none work. Specifically, I have tried preformating the cells in a new spreadsheet as text before I paste. The leading zeros are dropped. I have tried paste special/text. All the columns from the source table end up in column A. Is there a way to disable auto format? Is there anything else I can try? Thanks, David "David Biddulph" wrote: The reason that you can't see any response to a custom format after you've used the LEFT function is that custom formats don't work on text strings, and LEFT returns a text result. If you want to convert the text value to a number use --LEFT(...) The reason that your LEFT function doesn't see your leading zeros is that the custom formatting affects only the display, not the underlying content, so when the formula refers to F2 it sees the number in the cell, not your formatted display version. If you want the LEFT function to see the formatted version, replace the F2 references by TEXT(F2,"000000000000"). -- David Biddulph "Carl Nash" <Carl wrote in message ... I am using Excel 2003. Excel is dropping the leading zeros from 12 digit UPC numbers I paste into a spreadsheet. I know how to restore the leading zeros using the special cell formatting option "000000000000" to make the number 12 digits, this pads the beginning of the number with leading zero's to fill it out to 12 digits. However, I also need to drop the last digit (the check digit) from the UPC numbers that I am pasting. When I use a formula (in this case =LEFT(F2,LEN(F2)-1); I apply this formula to the entire column to the right of the column with the UPC numbers [column F] and it drops the last digit) to drop the last digit from the UPC, the numbers that are generated unfortunately seem to have dropped the lead zeros again. And once I have formatted this column with the formula to generate the UPC minus the check digit, I don't seem to be able to apply a custom number format to the cells to make them 11 digits, thus restoring the lost leading zeros. Is there any way to make Excel quit dropping leading zeros? Or is there any way to both have a formula applied to a column AND a custom number format? I am totally stuck here. Thanks for any help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop automatically removing leading zeros? | Excel Discussion (Misc queries) | |||
How do you stop excel removing the leading zeros in a cell? | Excel Discussion (Misc queries) | |||
Stop Excel from stripping out leading zeros when saving as CSV | Excel Discussion (Misc queries) | |||
Stop Excel from dropping the 0 in the beginning of a number | Setting up and Configuration of Excel | |||
Excel file saved as csv - dropping zeros | Excel Discussion (Misc queries) |