I have an XML file that I am opening in excel. The file looks good except for one thing. Ther is a column that has a number of 324233016893 but excel is displaying 3.24233E+11. I do not mind that it displays this however I am then saving the file to a Tab Text Delimited file and the 3.24233E+11 format is holding. How do I stop this?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How do I stop E+11 Numbers
- Thread starter borsker
- Start date
StuckInTheMiddle
Programmer
Make sure you format the cells as NUMBER not as GENERAL, they will then save as true numbers.
A,
A,
They are true numbers, but when you output it to a CSV you would be loosing 6 digits of precision if you let them stay in scientific notation.
[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]
[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]
- Thread starter
- #5
Thanks for the help,
The real problem was that this was a customer account number not a real number, if that makes sense.
The number thing worked great. But since I am on the subject of excel autoformating. Do any of you know how to keep a date from changing even if you format the cell as text. for example I write in March 3, 2006 and excel loves to change it to 3-Mar-06 or when I enter 3/3/2006 and save the file as dbase4 it gives me a number 38779. I hate that. Seems the only way I can keep any formatting is add the ' character in front. Is there any way around this.
The real problem was that this was a customer account number not a real number, if that makes sense.
The number thing worked great. But since I am on the subject of excel autoformating. Do any of you know how to keep a date from changing even if you format the cell as text. for example I write in March 3, 2006 and excel loves to change it to 3-Mar-06 or when I enter 3/3/2006 and save the file as dbase4 it gives me a number 38779. I hate that. Seems the only way I can keep any formatting is add the ' character in front. Is there any way around this.
SkipVought
Programmer
Why do Dates and Times seem to be so much trouble? faq68-5827
When you append a ' to the front of your date, it destroys the date and renders it virtually useless as a date. 38779 is a date serial value than can be formatted to display a date in any number of interesting ways like Friday, Mar 03, 2006 using 'dddd, mmm dd, yyyy' as a format.
Skip,
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
[red]Be Advised![/red] Coeds studying ancient Egyptian plumbing, might be known as...Pharaoh Faucet Majors
![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")
StuckInTheMiddle
Programmer
Skip,
You certainly get good mileage out of that date FAQ of yours
A,
You certainly get good mileage out of that date FAQ of yours
A,
SkipVought
Programmer
Yea. Lately, since my tuneup, I've been getting 27.3 mpg! ;-)
Skip,
[red]Be Advised![/red] Coeds studying ancient Egyptian plumbing, might be known as...Pharaoh Faucet Majors
- Status
Similar threads
- Locked
- Question
- Locked
- Question