Re: Formatting numbers with leading and trailing zero's
- From: "Rick Rothstein \(MVP - VB\)" <rick.newsNO.SPAM@xxxxxxxxxxxxxxxxxx>
- Date: Tue, 17 Jun 2008 11:27:48 -0400
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" <Victoria@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:E2C19127-AB3D-4A6B-9019-5AA38FCDDAA1@xxxxxxxxxxxxxxxx
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" <Victoria@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B7E0ABDA-7782-42C8-BCE4-BA7069DD0E55@xxxxxxxxxxxxxxxx
> 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
.
- Follow-Ups:
- Re: Formatting numbers with leading and trailing zero's
- From: Victoria
- Re: Formatting numbers with leading and trailing zero's
- References:
- Re: Formatting numbers with leading and trailing zero's
- From: David Biddulph
- Re: Formatting numbers with leading and trailing zero's
- From: Victoria
- Re: Formatting numbers with leading and trailing zero's
- Prev by Date: Re: Formatting numbers with leading and trailing zero's
- Next by Date: Re: Form on Excel
- Previous by thread: Re: Formatting numbers with leading and trailing zero's
- Next by thread: Re: Formatting numbers with leading and trailing zero's
- Index(es):
Relevant Pages
|
