Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting numbers with leading and trailing zero's
Hello,
I am exporting a data file from another program into a text file and then into an Exel file. The numbers are correct in the original program, but once the data gets to the .txt file or xls. file they lose any preceeding zero's or trailing zero's. Examples: Original program shows 250.70 or 02.06. After getting to .txt or .xls they are 250.7 or 2.06. I know that I can format cells in Exel to either Text or Custom and then if I manually add the missing zero's in each cell, the data stays like that. However, I want to be able to run a fix on the .txt or .xls file and have it automatically find and correct the entries that transferred incorrectly. (i.e., without the zero's) Does anyone know if there is a way of doing this, so as not to have to manually format cells and manually correct each entry? Thanks! Victoria |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting numbers with leading and trailing zero's
If you have stored the values in Excel as numbers, then Excel has no way of
knowing that you regard them as "incorrect". If you want to format all the numbers with 2 decimal places you can do that. If you want to format as 00.00 that will deal with your 02.06. Your best way of keeping it in accordance with your imported text file is to import the columns as text, not as general or number. As you apparently didn't do that the first time, then you may prefer to import again. -- David Biddulph "Victoria" wrote in message ... Hello, I am exporting a data file from another program into a text file and then into an Exel file. The numbers are correct in the original program, but once the data gets to the .txt file or xls. file they lose any preceeding zero's or trailing zero's. Examples: Original program shows 250.70 or 02.06. After getting to .txt or .xls they are 250.7 or 2.06. I know that I can format cells in Exel to either Text or Custom and then if I manually add the missing zero's in each cell, the data stays like that. However, I want to be able to run a fix on the .txt or .xls file and have it automatically find and correct the entries that transferred incorrectly. (i.e., without the zero's) Does anyone know if there is a way of doing this, so as not to have to manually format cells and manually correct each entry? Thanks! Victoria |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting numbers with leading and trailing zero's
Hi David,
Unfortunately if I format all numbers with 2 decimal places it takes care of the 02.06 problem, but in the case where I really want the value to be 954.0, I get 954.00. I did as you suggested and I imported the file again, this time choosing Text instead of General, but unfortunately none of the numbers kept the zero's as in the original data file. Do you know of anything else I can try? Victoria "David Biddulph" wrote: If you have stored the values in Excel as numbers, then Excel has no way of knowing that you regard them as "incorrect". If you want to format all the numbers with 2 decimal places you can do that. If you want to format as 00.00 that will deal with your 02.06. Your best way of keeping it in accordance with your imported text file is to import the columns as text, not as general or number. As you apparently didn't do that the first time, then you may prefer to import again. -- David Biddulph "Victoria" wrote in message ... Hello, I am exporting a data file from another program into a text file and then into an Exel file. The numbers are correct in the original program, but once the data gets to the .txt file or xls. file they lose any preceeding zero's or trailing zero's. Examples: Original program shows 250.70 or 02.06. After getting to .txt or .xls they are 250.7 or 2.06. I know that I can format cells in Exel to either Text or Custom and then if I manually add the missing zero's in each cell, the data stays like that. However, I want to be able to run a fix on the .txt or .xls file and have it automatically find and correct the entries that transferred incorrectly. (i.e., without the zero's) Does anyone know if there is a way of doing this, so as not to have to manually format cells and manually correct each entry? Thanks! Victoria |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting numbers with leading and trailing zero's
If the missing leading and/or trailing zeroes were consistent in how many
were missing, then the suggestion David gave you would be the way to restore them; however, if the number of leading and/or trailing zeroes can vary within a given column, then there is nothing you can do to restore the information that is not there. How could you? One cell could be missing 1 zero, the next 3 zeroes, and the next 2 zeroes... how would anyone know? The key to your problem is to fix the problem in the "other program" that is generating the original text file... it is where the data is being screwed up at. That program needs to be set up to export the data as displayed within itself, and not as a pure numerical value. Rick "Victoria" wrote in message ... Hi David, Unfortunately if I format all numbers with 2 decimal places it takes care of the 02.06 problem, but in the case where I really want the value to be 954.0, I get 954.00. I did as you suggested and I imported the file again, this time choosing Text instead of General, but unfortunately none of the numbers kept the zero's as in the original data file. Do you know of anything else I can try? Victoria "David Biddulph" wrote: If you have stored the values in Excel as numbers, then Excel has no way of knowing that you regard them as "incorrect". If you want to format all the numbers with 2 decimal places you can do that. If you want to format as 00.00 that will deal with your 02.06. Your best way of keeping it in accordance with your imported text file is to import the columns as text, not as general or number. As you apparently didn't do that the first time, then you may prefer to import again. -- David Biddulph "Victoria" wrote in message ... Hello, I am exporting a data file from another program into a text file and then into an Exel file. The numbers are correct in the original program, but once the data gets to the .txt file or xls. file they lose any preceeding zero's or trailing zero's. Examples: Original program shows 250.70 or 02.06. After getting to .txt or .xls they are 250.7 or 2.06. I know that I can format cells in Exel to either Text or Custom and then if I manually add the missing zero's in each cell, the data stays like that. However, I want to be able to run a fix on the .txt or .xls file and have it automatically find and correct the entries that transferred incorrectly. (i.e., without the zero's) Does anyone know if there is a way of doing this, so as not to have to manually format cells and manually correct each entry? Thanks! Victoria |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting numbers with leading and trailing zero's
It works OK for me.
Are you sure that you selected text as the format for *each column separately* in the text import wizard? Perhaps you need to talk us through the sequence of steps you went through, and at which stage the problem occurred. -- David Biddulph "Victoria" wrote in message ... Hi David, Unfortunately if I format all numbers with 2 decimal places it takes care of the 02.06 problem, but in the case where I really want the value to be 954.0, I get 954.00. I did as you suggested and I imported the file again, this time choosing Text instead of General, but unfortunately none of the numbers kept the zero's as in the original data file. Do you know of anything else I can try? Victoria "David Biddulph" wrote: If you have stored the values in Excel as numbers, then Excel has no way of knowing that you regard them as "incorrect". If you want to format all the numbers with 2 decimal places you can do that. If you want to format as 00.00 that will deal with your 02.06. Your best way of keeping it in accordance with your imported text file is to import the columns as text, not as general or number. As you apparently didn't do that the first time, then you may prefer to import again. -- David Biddulph "Victoria" wrote in message ... Hello, I am exporting a data file from another program into a text file and then into an Exel file. The numbers are correct in the original program, but once the data gets to the .txt file or xls. file they lose any preceeding zero's or trailing zero's. Examples: Original program shows 250.70 or 02.06. After getting to .txt or .xls they are 250.7 or 2.06. I know that I can format cells in Exel to either Text or Custom and then if I manually add the missing zero's in each cell, the data stays like that. However, I want to be able to run a fix on the .txt or .xls file and have it automatically find and correct the entries that transferred incorrectly. (i.e., without the zero's) Does anyone know if there is a way of doing this, so as not to have to manually format cells and manually correct each entry? Thanks! Victoria |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting numbers with leading and trailing zero's
David,
It worked!! I didn't realize I had to click on each column and format each column in the import wizard to Text separately. Whew, I learned something there. This completely solves my problem. Thank you very much for your prompt replies and suggestions. Have a great day, Victoria "David Biddulph" wrote: It works OK for me. Are you sure that you selected text as the format for *each column separately* in the text import wizard? Perhaps you need to talk us through the sequence of steps you went through, and at which stage the problem occurred. -- David Biddulph "Victoria" wrote in message ... Hi David, Unfortunately if I format all numbers with 2 decimal places it takes care of the 02.06 problem, but in the case where I really want the value to be 954.0, I get 954.00. I did as you suggested and I imported the file again, this time choosing Text instead of General, but unfortunately none of the numbers kept the zero's as in the original data file. Do you know of anything else I can try? Victoria "David Biddulph" wrote: If you have stored the values in Excel as numbers, then Excel has no way of knowing that you regard them as "incorrect". If you want to format all the numbers with 2 decimal places you can do that. If you want to format as 00.00 that will deal with your 02.06. Your best way of keeping it in accordance with your imported text file is to import the columns as text, not as general or number. As you apparently didn't do that the first time, then you may prefer to import again. -- David Biddulph "Victoria" wrote in message ... Hello, I am exporting a data file from another program into a text file and then into an Exel file. The numbers are correct in the original program, but once the data gets to the .txt file or xls. file they lose any preceeding zero's or trailing zero's. Examples: Original program shows 250.70 or 02.06. After getting to .txt or .xls they are 250.7 or 2.06. I know that I can format cells in Exel to either Text or Custom and then if I manually add the missing zero's in each cell, the data stays like that. However, I want to be able to run a fix on the .txt or .xls file and have it automatically find and correct the entries that transferred incorrectly. (i.e., without the zero's) Does anyone know if there is a way of doing this, so as not to have to manually format cells and manually correct each entry? Thanks! Victoria |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting numbers with leading and trailing zero's
Hi Rick,
My numbers are inconsistent so that is why I couldn't format them with a specific decimal point. Fortunately, David helped me realize that I wasn't formatting each column in the text import wizard individually, thus not solving my problem. It now works! Thanks so much for your assistance. Have a nice day, Victoria "Rick Rothstein (MVP - VB)" wrote: If the missing leading and/or trailing zeroes were consistent in how many were missing, then the suggestion David gave you would be the way to restore them; however, if the number of leading and/or trailing zeroes can vary within a given column, then there is nothing you can do to restore the information that is not there. How could you? One cell could be missing 1 zero, the next 3 zeroes, and the next 2 zeroes... how would anyone know? The key to your problem is to fix the problem in the "other program" that is generating the original text file... it is where the data is being screwed up at. That program needs to be set up to export the data as displayed within itself, and not as a pure numerical value. Rick "Victoria" wrote in message ... Hi David, Unfortunately if I format all numbers with 2 decimal places it takes care of the 02.06 problem, but in the case where I really want the value to be 954.0, I get 954.00. I did as you suggested and I imported the file again, this time choosing Text instead of General, but unfortunately none of the numbers kept the zero's as in the original data file. Do you know of anything else I can try? Victoria "David Biddulph" wrote: If you have stored the values in Excel as numbers, then Excel has no way of knowing that you regard them as "incorrect". If you want to format all the numbers with 2 decimal places you can do that. If you want to format as 00.00 that will deal with your 02.06. Your best way of keeping it in accordance with your imported text file is to import the columns as text, not as general or number. As you apparently didn't do that the first time, then you may prefer to import again. -- David Biddulph "Victoria" wrote in message ... Hello, I am exporting a data file from another program into a text file and then into an Exel file. The numbers are correct in the original program, but once the data gets to the .txt file or xls. file they lose any preceeding zero's or trailing zero's. Examples: Original program shows 250.70 or 02.06. After getting to .txt or .xls they are 250.7 or 2.06. I know that I can format cells in Exel to either Text or Custom and then if I manually add the missing zero's in each cell, the data stays like that. However, I want to be able to run a fix on the .txt or .xls file and have it automatically find and correct the entries that transferred incorrectly. (i.e., without the zero's) Does anyone know if there is a way of doing this, so as not to have to manually format cells and manually correct each entry? Thanks! Victoria |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting numbers with leading and trailing zero's
Glad it worked.
-- David Biddulph "Victoria" wrote in message ... David, It worked!! I didn't realize I had to click on each column and format each column in the import wizard to Text separately. Whew, I learned something there. This completely solves my problem. Thank you very much for your prompt replies and suggestions. Have a great day, Victoria "David Biddulph" wrote: It works OK for me. Are you sure that you selected text as the format for *each column separately* in the text import wizard? Perhaps you need to talk us through the sequence of steps you went through, and at which stage the problem occurred. -- David Biddulph "Victoria" wrote in message ... Hi David, Unfortunately if I format all numbers with 2 decimal places it takes care of the 02.06 problem, but in the case where I really want the value to be 954.0, I get 954.00. I did as you suggested and I imported the file again, this time choosing Text instead of General, but unfortunately none of the numbers kept the zero's as in the original data file. Do you know of anything else I can try? Victoria "David Biddulph" wrote: If you have stored the values in Excel as numbers, then Excel has no way of knowing that you regard them as "incorrect". If you want to format all the numbers with 2 decimal places you can do that. If you want to format as 00.00 that will deal with your 02.06. Your best way of keeping it in accordance with your imported text file is to import the columns as text, not as general or number. As you apparently didn't do that the first time, then you may prefer to import again. -- David Biddulph "Victoria" wrote in message ... Hello, I am exporting a data file from another program into a text file and then into an Exel file. The numbers are correct in the original program, but once the data gets to the .txt file or xls. file they lose any preceeding zero's or trailing zero's. Examples: Original program shows 250.70 or 02.06. After getting to .txt or .xls they are 250.7 or 2.06. I know that I can format cells in Exel to either Text or Custom and then if I manually add the missing zero's in each cell, the data stays like that. However, I want to be able to run a fix on the .txt or .xls file and have it automatically find and correct the entries that transferred incorrectly. (i.e., without the zero's) Does anyone know if there is a way of doing this, so as not to have to manually format cells and manually correct each entry? Thanks! Victoria |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Leading Zero's in Excel | Excel Discussion (Misc queries) | |||
Leading zero's missing for SSN numbers... | Excel Discussion (Misc queries) | |||
How to add leading zero's | Excel Worksheet Functions | |||
How to get the leading zero's added in a document | Excel Worksheet Functions | |||
Leading zero's | Excel Discussion (Misc queries) |