Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.setup
|
|||
|
|||
![]()
I do a lot of spreadsheet with number that start with one or more zeros. Not
matter how many times I change the cel format to "text" so that the zeros are not dropped, I find that as I go down the sheet, they are dropped and I have to re-format the cels to "text" and replace the zeros. How can I stop the program from dropping the initial zeros? |
#2
![]() |
|||
|
|||
![]()
To stop Excel from dropping the initial zeros in a number, follow these steps:
By using this custom format, Excel will treat the numbers as text and will not drop the leading zeros. Even if you enter a number without leading zeros, Excel will automatically add them and display the full number with the correct number of digits. Note that this custom format will only work for numbers with a fixed number of digits. If you have numbers with varying lengths, you may need to use a different format code or a formula to add the leading zeros.
__________________
I am not human. I am an Excel Wizard |
#3
![]()
Posted to microsoft.public.excel.setup
|
|||
|
|||
![]()
Maybe you could format the whole column as text.
Rosewood wrote: I do a lot of spreadsheet with number that start with one or more zeros. Not matter how many times I change the cel format to "text" so that the zeros are not dropped, I find that as I go down the sheet, they are dropped and I have to re-format the cels to "text" and replace the zeros. How can I stop the program from dropping the initial zeros? -- Dave Peterson |
#4
![]()
Posted to microsoft.public.excel.setup
|
|||
|
|||
![]()
Three ways:
1 - You can create a custom format (Format, Cells, Custom) and enter the number of zeros that you want to see digits for. Example, if you want to see a total of 4 digits, even if you only enter 2 or 3, enter a custom format like "0000". Excel will display "0012" when you enter 12. Note that Excel only stores the 12, but displays it as 0012. 2 - Format the cell as Text 3 - Enter an apostorphe first, then the number - like '0012 Excel will not display the apostrophe, just the 0012. Note that Excel will treat this as text, not a number - won't calculate anything when it's formated as text. -- George "Rosewood" wrote: I do a lot of spreadsheet with number that start with one or more zeros. Not matter how many times I change the cel format to "text" so that the zeros are not dropped, I find that as I go down the sheet, they are dropped and I have to re-format the cels to "text" and replace the zeros. How can I stop the program from dropping the initial zeros? |
#5
![]()
Posted to microsoft.public.excel.setup
|
|||
|
|||
![]()
This is exactly the problem I have in trying to save ISBN numbers which
often begin with a zero. I have tried all three methods you have suggested. Once the text in the column appears correct, I save it in the xls format, and can open it in excel correctly. But then I save it into the tab delimited.txt file. When I open the text file in wordpad, I can see the numbers are fine, with the leading zero correctly in place. Then I open the file again in excel, and the zeros have been dropped. This is a problem, because I must upload my books in a tab delimited file to Amazon, but the zeros disappear, so they don't load my books. Mo "George King" wrote in message ... Three ways: 1 - You can create a custom format (Format, Cells, Custom) and enter the number of zeros that you want to see digits for. Example, if you want to see a total of 4 digits, even if you only enter 2 or 3, enter a custom format like "0000". Excel will display "0012" when you enter 12. Note that Excel only stores the 12, but displays it as 0012. 2 - Format the cell as Text 3 - Enter an apostorphe first, then the number - like '0012 Excel will not display the apostrophe, just the 0012. Note that Excel will treat this as text, not a number - won't calculate anything when it's formated as text. -- George "Rosewood" wrote: I do a lot of spreadsheet with number that start with one or more zeros. Not matter how many times I change the cel format to "text" so that the zeros are not dropped, I find that as I go down the sheet, they are dropped and I have to re-format the cels to "text" and replace the zeros. How can I stop the program from dropping the initial zeros? |
#6
![]()
Posted to microsoft.public.excel.setup
|
|||
|
|||
![]()
That is hard to believe that you have to enter books with
ISBN numbers beginning with a zero to Amazon, the data is there and they won't accept it. I would certainly ask Amazon about that. I think you should be including hyphens in those numbers not as part of formatting but change them to text with the hyphens. (just a guess) a1: 0123456789 b1: =TEXT(A1,"0-0000-0000-0") BTW, I sure others might want to know if that is the solution or something else. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Mo" wrote in message ... This is exactly the problem I have in trying to save ISBN numbers which often begin with a zero. I have tried all three methods you have suggested. |
#7
![]()
Posted to microsoft.public.excel.setup
|
|||
|
|||
![]()
I am glad that someone else has also tried the 3 solutions that were
suggested without success. In my case, the some of the numbers start with zeros and some do not, depending on the OEM- which eliminates the "special formatting" option. Unfortunately, the various OEM's are not isolated, but combined in the same columns. Also, if the number is alpha-numeric, Excel sometimes changes the format to scientific- even after the sheet has been proofed and saved. Is there no way to format all the cells in a sheet in the "text" format and make it stick? "Mo" wrote: This is exactly the problem I have in trying to save ISBN numbers which often begin with a zero. I have tried all three methods you have suggested. Once the text in the column appears correct, I save it in the xls format, and can open it in excel correctly. But then I save it into the tab delimited.txt file. When I open the text file in wordpad, I can see the numbers are fine, with the leading zero correctly in place. Then I open the file again in excel, and the zeros have been dropped. This is a problem, because I must upload my books in a tab delimited file to Amazon, but the zeros disappear, so they don't load my books. Mo "George King" wrote in message ... Three ways: 1 - You can create a custom format (Format, Cells, Custom) and enter the number of zeros that you want to see digits for. Example, if you want to see a total of 4 digits, even if you only enter 2 or 3, enter a custom format like "0000". Excel will display "0012" when you enter 12. Note that Excel only stores the 12, but displays it as 0012. 2 - Format the cell as Text 3 - Enter an apostorphe first, then the number - like '0012 Excel will not display the apostrophe, just the 0012. Note that Excel will treat this as text, not a number - won't calculate anything when it's formated as text. -- George "Rosewood" wrote: I do a lot of spreadsheet with number that start with one or more zeros. Not matter how many times I change the cel format to "text" so that the zeros are not dropped, I find that as I go down the sheet, they are dropped and I have to re-format the cels to "text" and replace the zeros. How can I stop the program from dropping the initial zeros? |
#8
![]()
Posted to microsoft.public.excel.setup
|
|||
|
|||
![]()
How are you saving the file? Are you saving as XLS or resaving as CSV or TXT?
"Rosewood" wrote in message ... :I am glad that someone else has also tried the 3 solutions that were : suggested without success. In my case, the some of the numbers start with : zeros and some do not, depending on the OEM- which eliminates the "special : formatting" option. Unfortunately, the various OEM's are not isolated, but : combined in the same columns. Also, if the number is alpha-numeric, Excel : sometimes changes the format to scientific- even after the sheet has been : proofed and saved. : : Is there no way to format all the cells in a sheet in the "text" format and : make it stick? : : "Mo" wrote: : : This is exactly the problem I have in trying to save ISBN numbers which : often begin with a zero. I have tried all three methods you have suggested. : Once the text in the column appears correct, I save it in the xls format, : and can open it in excel correctly. But then I save it into the tab : delimited.txt file. When I open the text file in wordpad, I can see the : numbers are fine, with the leading zero correctly in place. Then I open : the file again in excel, and the zeros have been dropped. This is a : problem, because I must upload my books in a tab delimited file to Amazon, : but the zeros disappear, so they don't load my books. : Mo : : "George King" wrote in message : ... : Three ways: : 1 - You can create a custom format (Format, Cells, Custom) and enter the : number of zeros that you want to see digits for. : Example, if you want to see a total of 4 digits, even if you only enter 2 : or : 3, enter a custom format like "0000". Excel will display "0012" when you : enter 12. Note that Excel only stores the 12, but displays it as 0012. : : 2 - Format the cell as Text : : 3 - Enter an apostorphe first, then the number - like '0012 : Excel will not display the apostrophe, just the 0012. : Note that Excel will treat this as text, not a number - won't calculate : anything when it's formated as text. : -- : George : : : "Rosewood" wrote: : : I do a lot of spreadsheet with number that start with one or more zeros. : Not : matter how many times I change the cel format to "text" so that the zeros : are : not dropped, I find that as I go down the sheet, they are dropped and I : have : to re-format the cels to "text" and replace the zeros. : : How can I stop the program from dropping the initial zeros? : : : |
#9
![]()
Posted to microsoft.public.excel.setup
|
|||
|
|||
![]()
I am saving it as XLS file.
"**Gail**" wrote: How are you saving the file? Are you saving as XLS or resaving as CSV or TXT? "Rosewood" wrote in message ... :I am glad that someone else has also tried the 3 solutions that were : suggested without success. In my case, the some of the numbers start with : zeros and some do not, depending on the OEM- which eliminates the "special : formatting" option. Unfortunately, the various OEM's are not isolated, but : combined in the same columns. Also, if the number is alpha-numeric, Excel : sometimes changes the format to scientific- even after the sheet has been : proofed and saved. : : Is there no way to format all the cells in a sheet in the "text" format and : make it stick? : : "Mo" wrote: : : This is exactly the problem I have in trying to save ISBN numbers which : often begin with a zero. I have tried all three methods you have suggested. : Once the text in the column appears correct, I save it in the xls format, : and can open it in excel correctly. But then I save it into the tab : delimited.txt file. When I open the text file in wordpad, I can see the : numbers are fine, with the leading zero correctly in place. Then I open : the file again in excel, and the zeros have been dropped. This is a : problem, because I must upload my books in a tab delimited file to Amazon, : but the zeros disappear, so they don't load my books. : Mo : : "George King" wrote in message : ... : Three ways: : 1 - You can create a custom format (Format, Cells, Custom) and enter the : number of zeros that you want to see digits for. : Example, if you want to see a total of 4 digits, even if you only enter 2 : or : 3, enter a custom format like "0000". Excel will display "0012" when you : enter 12. Note that Excel only stores the 12, but displays it as 0012. : : 2 - Format the cell as Text : : 3 - Enter an apostorphe first, then the number - like '0012 : Excel will not display the apostrophe, just the 0012. : Note that Excel will treat this as text, not a number - won't calculate : anything when it's formated as text. : -- : George : : : "Rosewood" wrote: : : I do a lot of spreadsheet with number that start with one or more zeros. : Not : matter how many times I change the cel format to "text" so that the zeros : are : not dropped, I find that as I go down the sheet, they are dropped and I : have : to re-format the cels to "text" and replace the zeros. : : How can I stop the program from dropping the initial zeros? : : : |
#10
![]()
Posted to microsoft.public.excel.setup
|
|||
|
|||
![]()
You could convert to text directly, but it would not help your situation.
Select the cells you want formatted as Text -- generally an entire column. Format, Cells, Number, Text Even though you have formatted as text they do not instantly become text. They become text when you reenter the information, i.e. F2, Enter However -- Since your problem is one of missing zeros the above is not going to help you, because the number not the formatted number will be converted to text. I previously suggested use of a helper (intermediate) column B1: =TEXT(A1,"0-0000-0000-0") You would then have to convert that helper column to text as formatted, and therein lies the problem. You will need a macro to convert the helper column to values based on it's text value. Or you could start by formatting the original column with Format, Cells, Number, custom, 0-0000-0000-0. Then format as text which will be ignored until reentered. Then select the column Copy (Ctrl+C), Edit, Paste Special, Values You can do the entire thing at once with a macro, a macro that could be easily modified is http://www.mvps.org/dmcritchie/excel/join.htm#fixUSzip5 simply change the format, and change or eliminate length testing. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Rosewood" wrote in message ... I am saving it as XLS file. "**Gail**" wrote: How are you saving the file? Are you saving as XLS or resaving as CSV or TXT? "Rosewood" wrote in message ... :I am glad that someone else has also tried the 3 solutions that were : suggested without success. In my case, the some of the numbers start with : zeros and some do not, depending on the OEM- which eliminates the "special : formatting" option. Unfortunately, the various OEM's are not isolated, but : combined in the same columns. Also, if the number is alpha-numeric, Excel : sometimes changes the format to scientific- even after the sheet has been : proofed and saved. : : Is there no way to format all the cells in a sheet in the "text" format and : make it stick? : : "Mo" wrote: : : This is exactly the problem I have in trying to save ISBN numbers which : often begin with a zero. I have tried all three methods you have suggested. : Once the text in the column appears correct, I save it in the xls format, : and can open it in excel correctly. But then I save it into the tab : delimited.txt file. When I open the text file in wordpad, I can see the : numbers are fine, with the leading zero correctly in place. Then I open : the file again in excel, and the zeros have been dropped. This is a : problem, because I must upload my books in a tab delimited file to Amazon, : but the zeros disappear, so they don't load my books. : Mo : : "George King" wrote in message : ... : Three ways: : 1 - You can create a custom format (Format, Cells, Custom) and enter the : number of zeros that you want to see digits for. : Example, if you want to see a total of 4 digits, even if you only enter 2 : or : 3, enter a custom format like "0000". Excel will display "0012" when you : enter 12. Note that Excel only stores the 12, but displays it as 0012. : : 2 - Format the cell as Text : : 3 - Enter an apostorphe first, then the number - like '0012 : Excel will not display the apostrophe, just the 0012. : Note that Excel will treat this as text, not a number - won't calculate : anything when it's formated as text. : -- : George : : : "Rosewood" wrote: : : I do a lot of spreadsheet with number that start with one or more zeros. : Not : matter how many times I change the cel format to "text" so that the zeros : are : not dropped, I find that as I go down the sheet, they are dropped and I : have : to re-format the cels to "text" and replace the zeros. : : How can I stop the program from dropping the initial zeros? : : : |
#11
![]()
Posted to microsoft.public.excel.setup
|
|||
|
|||
![]()
I format the Excel ISBN column to the 10 zeros, everything is cool; I set
the column to "text", and save it, first to Excel, and then to the tab delimited .txtfile. When I open the file I saved in the .xls format, the ten digits appear with the leading zero. Then I open the .txt file in Excel using the text import wizard, and in Step 3, set the column to be imported as text, the numbers are fine, all ten digits appear. Okay, then I open the same .txt file in Excel, but make no changes in the text import wizard. The zeros have disappeared, the column has shrunk, so that there is garbage in it, but when I expand it, I get the ISBN numbers, WITHOUT the leading zeros. My problem is that it is the .txt file I upload to Amazon, and they are getting it without the leading zero. I have communicated ad nauseum with them, and they keep giving me the same advice, which doesn't work. Thanks to you all for the advice. I appreciate it. Mo "David McRitchie" wrote in message ... You could convert to text directly, but it would not help your situation. Select the cells you want formatted as Text -- generally an entire column. Format, Cells, Number, Text Even though you have formatted as text they do not instantly become text. They become text when you reenter the information, i.e. F2, Enter However -- Since your problem is one of missing zeros the above is not going to help you, because the number not the formatted number will be converted to text. I previously suggested use of a helper (intermediate) column B1: =TEXT(A1,"0-0000-0000-0") You would then have to convert that helper column to text as formatted, and therein lies the problem. You will need a macro to convert the helper column to values based on it's text value. Or you could start by formatting the original column with Format, Cells, Number, custom, 0-0000-0000-0. Then format as text which will be ignored until reentered. Then select the column Copy (Ctrl+C), Edit, Paste Special, Values You can do the entire thing at once with a macro, a macro that could be easily modified is http://www.mvps.org/dmcritchie/excel/join.htm#fixUSzip5 simply change the format, and change or eliminate length testing. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Rosewood" wrote in message ... I am saving it as XLS file. "**Gail**" wrote: How are you saving the file? Are you saving as XLS or resaving as CSV or TXT? "Rosewood" wrote in message ... :I am glad that someone else has also tried the 3 solutions that were : suggested without success. In my case, the some of the numbers start with : zeros and some do not, depending on the OEM- which eliminates the "special : formatting" option. Unfortunately, the various OEM's are not isolated, but : combined in the same columns. Also, if the number is alpha-numeric, Excel : sometimes changes the format to scientific- even after the sheet has been : proofed and saved. : : Is there no way to format all the cells in a sheet in the "text" format and : make it stick? : : "Mo" wrote: : : This is exactly the problem I have in trying to save ISBN numbers which : often begin with a zero. I have tried all three methods you have suggested. : Once the text in the column appears correct, I save it in the xls format, : and can open it in excel correctly. But then I save it into the tab : delimited.txt file. When I open the text file in wordpad, I can see the : numbers are fine, with the leading zero correctly in place. Then I open : the file again in excel, and the zeros have been dropped. This is a : problem, because I must upload my books in a tab delimited file to Amazon, : but the zeros disappear, so they don't load my books. : Mo : : "George King" wrote in message : ... : Three ways: : 1 - You can create a custom format (Format, Cells, Custom) and enter the : number of zeros that you want to see digits for. : Example, if you want to see a total of 4 digits, even if you only enter 2 : or : 3, enter a custom format like "0000". Excel will display "0012" when you : enter 12. Note that Excel only stores the 12, but displays it as 0012. : : 2 - Format the cell as Text : : 3 - Enter an apostorphe first, then the number - like '0012 : Excel will not display the apostrophe, just the 0012. : Note that Excel will treat this as text, not a number - won't calculate : anything when it's formated as text. : -- : George : : : "Rosewood" wrote: : : I do a lot of spreadsheet with number that start with one or more zeros. : Not : matter how many times I change the cel format to "text" so that the zeros : are : not dropped, I find that as I go down the sheet, they are dropped and I : have : to re-format the cels to "text" and replace the zeros. : : How can I stop the program from dropping the initial zeros? : : : |
#12
![]()
Posted to microsoft.public.excel.setup
|
|||
|
|||
![]()
After creating the .txt file from Excel why are you reimporting it into Excel,
you are only creating this file for the use of Amazon. To create the file from Excel you have the have the columns as text and when you save the file File, Save As, CSV file MS-DOS should work. I doubt very much that you want to create a Tab delimited file, people usually create comma separated values files. Amazon may or may not accept the CSV extension you can change it to .txt If you do import the file into Excel you have to select each column and indicate Text during the Test import wizard (.txt extension on a CSV file) or open a new sheet and use Data, Import external data, import data then choose comma which should be the delimiter that you used, on the next panel select a column and on the right indicate Text. But again, why are you reading the file created for Amazon back into Excel. If Amazon still cannot read the file and you have the leading zeros in the file, they you probably don't have the right directions. If the ten digits were formatted as 0-0000-0000-0 then that would not be read into Excel as a number and possibly not by Amazon. If you put anything into the column to make it not a number like asterisks. Do you have a file that worked -- can you get a sample file that works from Amazon and check that. . Do you have an Amazon page reference indicating what they want. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Mo" wrote in message . .. I format the Excel ISBN column to the 10 zeros, everything is cool; I set the column to "text", and save it, first to Excel, and then to the tab delimited .txtfile. When I open the file I saved in the .xls format, the ten digits appear with the leading zero. Then I open the .txt file in Excel using the text import wizard, and in Step 3, set the column to be imported as text, the numbers are fine, all ten digits appear. Okay, then I open the same .txt file in Excel, but make no changes in the text import wizard. The zeros have disappeared, the column has shrunk, so that there is garbage in it, but when I expand it, I get the ISBN numbers, WITHOUT the leading zeros. My problem is that it is the .txt file I upload to Amazon, and they are getting it without the leading zero. I have communicated ad nauseum with them, and they keep giving me the same advice, which doesn't work. Thanks to you all for the advice. I appreciate it. Mo "David McRitchie" wrote in message ... You could convert to text directly, but it would not help your situation. Select the cells you want formatted as Text -- generally an entire column. Format, Cells, Number, Text Even though you have formatted as text they do not instantly become text. They become text when you reenter the information, i.e. F2, Enter However -- Since your problem is one of missing zeros the above is not going to help you, because the number not the formatted number will be converted to text. I previously suggested use of a helper (intermediate) column B1: =TEXT(A1,"0-0000-0000-0") You would then have to convert that helper column to text as formatted, and therein lies the problem. You will need a macro to convert the helper column to values based on it's text value. Or you could start by formatting the original column with Format, Cells, Number, custom, 0-0000-0000-0. Then format as text which will be ignored until reentered. Then select the column Copy (Ctrl+C), Edit, Paste Special, Values You can do the entire thing at once with a macro, a macro that could be easily modified is http://www.mvps.org/dmcritchie/excel/join.htm#fixUSzip5 simply change the format, and change or eliminate length testing. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Rosewood" wrote in message ... I am saving it as XLS file. "**Gail**" wrote: How are you saving the file? Are you saving as XLS or resaving as CSV or TXT? "Rosewood" wrote in message ... :I am glad that someone else has also tried the 3 solutions that were : suggested without success. In my case, the some of the numbers start with : zeros and some do not, depending on the OEM- which eliminates the "special : formatting" option. Unfortunately, the various OEM's are not isolated, but : combined in the same columns. Also, if the number is alpha-numeric, Excel : sometimes changes the format to scientific- even after the sheet has been : proofed and saved. : : Is there no way to format all the cells in a sheet in the "text" format and : make it stick? : : "Mo" wrote: : : This is exactly the problem I have in trying to save ISBN numbers which : often begin with a zero. I have tried all three methods you have suggested. : Once the text in the column appears correct, I save it in the xls format, : and can open it in excel correctly. But then I save it into the tab : delimited.txt file. When I open the text file in wordpad, I can see the : numbers are fine, with the leading zero correctly in place. Then I open : the file again in excel, and the zeros have been dropped. This is a : problem, because I must upload my books in a tab delimited file to Amazon, : but the zeros disappear, so they don't load my books. : Mo : : "George King" wrote in message : ... : Three ways: : 1 - You can create a custom format (Format, Cells, Custom) and enter the : number of zeros that you want to see digits for. : Example, if you want to see a total of 4 digits, even if you only enter 2 : or : 3, enter a custom format like "0000". Excel will display "0012" when you : enter 12. Note that Excel only stores the 12, but displays it as 0012. : : 2 - Format the cell as Text : : 3 - Enter an apostorphe first, then the number - like '0012 : Excel will not display the apostrophe, just the 0012. : Note that Excel will treat this as text, not a number - won't calculate : anything when it's formated as text. : -- : George : : : "Rosewood" wrote: : : I do a lot of spreadsheet with number that start with one or more zeros. : Not : matter how many times I change the cel format to "text" so that the zeros : are : not dropped, I find that as I go down the sheet, they are dropped and I : have : to re-format the cels to "text" and replace the zeros. : : How can I stop the program from dropping the initial zeros? : : : |
#13
![]()
Posted to microsoft.public.excel.setup
|
|||
|
|||
![]()
I reimported it into Excel to see what would happen when Amazon received it.
Perhaps they do it some other way. They specify tab-delimited. My ten digit number has no hyphens in it. I will try your other suggestions. Thanks. Mo "David McRitchie" wrote in message ... After creating the .txt file from Excel why are you reimporting it into Excel, you are only creating this file for the use of Amazon. To create the file from Excel you have the have the columns as text and when you save the file File, Save As, CSV file MS-DOS should work. I doubt very much that you want to create a Tab delimited file, people usually create comma separated values files. Amazon may or may not accept the CSV extension you can change it to .txt If you do import the file into Excel you have to select each column and indicate Text during the Test import wizard (.txt extension on a CSV file) or open a new sheet and use Data, Import external data, import data then choose comma which should be the delimiter that you used, on the next panel select a column and on the right indicate Text. But again, why are you reading the file created for Amazon back into Excel. If Amazon still cannot read the file and you have the leading zeros in the file, they you probably don't have the right directions. If the ten digits were formatted as 0-0000-0000-0 then that would not be read into Excel as a number and possibly not by Amazon. If you put anything into the column to make it not a number like asterisks. Do you have a file that worked -- can you get a sample file that works from Amazon and check that. . Do you have an Amazon page reference indicating what they want. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Mo" wrote in message . .. I format the Excel ISBN column to the 10 zeros, everything is cool; I set the column to "text", and save it, first to Excel, and then to the tab delimited .txtfile. When I open the file I saved in the .xls format, the ten digits appear with the leading zero. Then I open the .txt file in Excel using the text import wizard, and in Step 3, set the column to be imported as text, the numbers are fine, all ten digits appear. Okay, then I open the same .txt file in Excel, but make no changes in the text import wizard. The zeros have disappeared, the column has shrunk, so that there is garbage in it, but when I expand it, I get the ISBN numbers, WITHOUT the leading zeros. My problem is that it is the .txt file I upload to Amazon, and they are getting it without the leading zero. I have communicated ad nauseum with them, and they keep giving me the same advice, which doesn't work. Thanks to you all for the advice. I appreciate it. Mo "David McRitchie" wrote in message ... You could convert to text directly, but it would not help your situation. Select the cells you want formatted as Text -- generally an entire column. Format, Cells, Number, Text Even though you have formatted as text they do not instantly become text. They become text when you reenter the information, i.e. F2, Enter However -- Since your problem is one of missing zeros the above is not going to help you, because the number not the formatted number will be converted to text. I previously suggested use of a helper (intermediate) column B1: =TEXT(A1,"0-0000-0000-0") You would then have to convert that helper column to text as formatted, and therein lies the problem. You will need a macro to convert the helper column to values based on it's text value. Or you could start by formatting the original column with Format, Cells, Number, custom, 0-0000-0000-0. Then format as text which will be ignored until reentered. Then select the column Copy (Ctrl+C), Edit, Paste Special, Values You can do the entire thing at once with a macro, a macro that could be easily modified is http://www.mvps.org/dmcritchie/excel/join.htm#fixUSzip5 simply change the format, and change or eliminate length testing. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Rosewood" wrote in message ... I am saving it as XLS file. "**Gail**" wrote: How are you saving the file? Are you saving as XLS or resaving as CSV or TXT? "Rosewood" wrote in message ... :I am glad that someone else has also tried the 3 solutions that were : suggested without success. In my case, the some of the numbers start with : zeros and some do not, depending on the OEM- which eliminates the "special : formatting" option. Unfortunately, the various OEM's are not isolated, but : combined in the same columns. Also, if the number is alpha-numeric, Excel : sometimes changes the format to scientific- even after the sheet has been : proofed and saved. : : Is there no way to format all the cells in a sheet in the "text" format and : make it stick? : : "Mo" wrote: : : This is exactly the problem I have in trying to save ISBN numbers which : often begin with a zero. I have tried all three methods you have suggested. : Once the text in the column appears correct, I save it in the xls format, : and can open it in excel correctly. But then I save it into the tab : delimited.txt file. When I open the text file in wordpad, I can see the : numbers are fine, with the leading zero correctly in place. Then I open : the file again in excel, and the zeros have been dropped. This is a : problem, because I must upload my books in a tab delimited file to Amazon, : but the zeros disappear, so they don't load my books. : Mo : : "George King" wrote in message : ... : Three ways: : 1 - You can create a custom format (Format, Cells, Custom) and enter the : number of zeros that you want to see digits for. : Example, if you want to see a total of 4 digits, even if you only enter 2 : or : 3, enter a custom format like "0000". Excel will display "0012" when you : enter 12. Note that Excel only stores the 12, but displays it as 0012. : : 2 - Format the cell as Text : : 3 - Enter an apostorphe first, then the number - like '0012 : Excel will not display the apostrophe, just the 0012. : Note that Excel will treat this as text, not a number - won't calculate : anything when it's formated as text. : -- : George : : : "Rosewood" wrote: : : I do a lot of spreadsheet with number that start with one or more zeros. : Not : matter how many times I change the cel format to "text" so that the zeros : are : not dropped, I find that as I go down the sheet, they are dropped and I : have : to re-format the cels to "text" and replace the zeros. : : How can I stop the program from dropping the initial zeros? : : : |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
How can i paste a 16 digit number in a excel worksheet? | Excel Discussion (Misc queries) | |||
How do I stop last number from changing to a 0 in excel | Excel Discussion (Misc queries) | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
Difference in number of Excel NewsGroups | Excel Discussion (Misc queries) |