Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-02-2021, 03:08 PM
GracieAllen GracieAllen is offline Make long numbers that aren't number stop being exponents Windows 10 Make long numbers that aren't number stop being exponents Office 2019
Novice
Make long numbers that aren't number stop being exponents
 
Join Date: Dec 2019
Posts: 29
GracieAllen is on a distinguished road
Default Make long numbers that aren't number stop being exponents

Excel 2019.



I have a couple applications that create .csv files when exporting. There is ABSOLUTELY NO control over how they export or what they export. They just dump everything. Including very long library card "numbers" and very long credit card "numbers" which Excel does bad things to.

They show up looking like this: 1.1011E+12. This column that contains email addresses, names, house addresses, other clearly text fields. AND THESE scientific notations. I've TRIED formatting the column, telling Excel the column is text. That doesn't work. I've tried telling the it the column is a number with zero decimal places. That makes the junk a long number, but the number is no longer accurate. It started as 16 digits ending with something like 2193 and ended with a 2190 or originally ended in 0237 and is now 0230. I don't KNOW if other digits in the string are correct or not, but I KNOW the last digit keeps getting changed.

HOW do I make Excel STOP this?
Reply With Quote
  #2  
Old 02-02-2021, 04:47 PM
joeu2004 joeu2004 is offline Make long numbers that aren't number stop being exponents Windows 7 32bit Make long numbers that aren't number stop being exponents Office 2007
Advanced Beginner
 
Join Date: Aug 2016
Posts: 32
joeu2004 is on a distinguished road
Default

AFAIK, the only work-around is to __import__ the file instead of opening it directly.


And contrary to popular advice, you do __not__ have to rename the file to have a ".txt" extension.


In the Import Text File wizard, when you get to the last (3rd) dialog box, select the column with the long "numbers" (not!), and select Text for the Column Data Format.
Reply With Quote
  #3  
Old 02-02-2021, 05:39 PM
GracieAllen GracieAllen is offline Make long numbers that aren't number stop being exponents Windows 10 Make long numbers that aren't number stop being exponents Office 2019
Novice
Make long numbers that aren't number stop being exponents
 
Join Date: Dec 2019
Posts: 29
GracieAllen is on a distinguished road
Default

Um... Ick.



I found an OLD discussion where this question got asked - somewhere around 2015 - or about 75 years ago in computer years. They got the same answer, but I figured after ALL THAT TIME Excel MUST have something a whole lot better than brute force, hoping you hit the right columns, during an import of a file that should be formatted so you don't have to import it.....


<heavy sigh>
Reply With Quote
  #4  
Old 02-02-2021, 10:45 PM
Purfleet Purfleet is offline Make long numbers that aren't number stop being exponents Windows 10 Make long numbers that aren't number stop being exponents Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Excel can only hold 15 significant digits in a number, so the end will just be truncated with zeros - very annoying.

From memory if you open a csv by clicking on it and your pc is configured to open csv's in excel it just works out what is best (and often isnt)

I would have thought that the CSV file would have the correct long number recorded correctly? If so can you import into excel and use the import wizard then in step 3 you can select Text.

If you can upload an example csv i can give it a test

Edit:
Whoops - i hadnt seen joeu2004's post. At least we agreed!
Reply With Quote
  #5  
Old 02-04-2021, 05:03 AM
GracieAllen GracieAllen is offline Make long numbers that aren't number stop being exponents Windows 10 Make long numbers that aren't number stop being exponents Office 2019
Novice
Make long numbers that aren't number stop being exponents
 
Join Date: Dec 2019
Posts: 29
GracieAllen is on a distinguished road
Default

Clicking on the csv files was what I was doing. Excel butchered a whole bunch of numbers, including some that were only 13 digits.


I finally found a sort-of import buried in Excel 2019 and when I used that the 1 time I've tried it so far I got numbers that weren't exponents...


Hopefully, that'll work most of the time.


I'm also HOPING I can change the way this information is recorded, so it gets exported with some accompanying text.


It's just one of those niggling, infuriating things that make what should be a trivial job more difficult.
Reply With Quote
  #6  
Old 02-05-2021, 05:10 AM
p45cal's Avatar
p45cal p45cal is offline Make long numbers that aren't number stop being exponents Windows 10 Make long numbers that aren't number stop being exponents Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by GracieAllen View Post
but I figured after ALL THAT TIME Excel MUST have something a whole lot better than brute force<heavy sigh>
It does, Power Query. Set it up well and you may not even have to click anything at all for it to update correctly.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Make long numbers that aren't number stop being exponents Index [TOC] numbers aren't right Dennis.n Word 3 01-05-2018 04:13 AM
Where to put Subroutine to make sure bookmarks aren't deleted so cross-references work mrsjetset Word VBA 5 06-29-2016 05:06 PM
Trying to make a long list sequential. icecream Word 2 01-03-2014 12:02 AM
Space between number and heading - how to make it consistent for larger numbers Dr Wu Word 4 05-09-2013 08:29 AM
Make long numbers that aren't number stop being exponents Long, 3 Column Table - Can I make Fit Into Page Columns? Rigwald Word Tables 9 08-07-2012 08:14 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:53 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft