Formatting numbers with leading and trailing zero's

[Victoria]

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

 

[David Biddulph]

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.

 

[Victoria]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[David Biddulph]

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.

 

[Victoria]

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

 

[Victoria]

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

 

[David Biddulph]

Glad it worked.
--
David Biddulph

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