Make long numbers that aren't number stop being exponents

[GracieAllen]

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?

[joeu2004]

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.

[GracieAllen]

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>

[Purfleet]

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!

[GracieAllen]

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.

[p45cal]

Originally Posted by GracieAllen

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.