Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 548
  • Last Modified:

Excel import of text file

When I open a text file (with separated fields) in Micrsoft Excel, it converts the number to something incorrect.

For example: The value 1189124350620307456 changes to 1.18912E+18. If I right click on the Excel field and 'Format Cell' and set the Category to 'Number' with Decimal places = 0,
it shows incorrect value 1189124350620300000.

So the original 1189124350620307456 changes to 1189124350620300000. Is there any way to fix this issue? screen
0
toooki
Asked:
toooki
  • 2
1 Solution
 
John HurstBusiness Consultant (Owner)Commented:
It is not a function of the import, but results from the fact that the maximum number of digits in Excel is 15. So that is what you got here.

See the Microsoft Support article on this below.

http://support.microsoft.com/kb/269370

.... Thinkpads_User
0
 
toookiAuthor Commented:
Thank you.

Also later someone said:
I tested it myself now, and a 32-digit string is converted into a number like 1.23457E+17 automatically.

The workaround then (proven to work in Excel XP):
- rename the file to .txt
- in Excel, use File>Open to open it, this will call the text import wizard
- after answering a few questions about format (delimited) and separator (,) you'll reach the screen where you can specify the format for each column. If you specify text for the column with these long numbers they won't be converted and still look like a 32-digit number.

It worked. Thank you.
0
 
John HurstBusiness Consultant (Owner)Commented:
@toooki - Thanks and I was happy to help.

.... Thinkpads_User
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now