SOLVED
Home

Convert txt to excel

%3CLINGO-SUB%20id%3D%22lingo-sub-174455%22%20slang%3D%22en-US%22%3EConvert%20txt%20to%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-174455%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%C2%B4m%20trying%20to%20convert%20a%20txt%20file%20which%20has%20numbers%20to%20an%20excel%20file%20so%20I%20can%20review%20the%20information%20easily.%20I'm%26nbsp%3Busing%20excel%202016%20so%20I%20go%20to%20data%2C%20get%20data%2C%20from%20txt%20file%20and%20the%20information%20pops%20up.%20The%20problem%20is%20that%20the%20numbers%20change.%20For%20example%20in%20the%20original%26nbsp%3Btxt%20a%20number%20could%20be%204551932613%20and%20then%20it%20populates%20in%20a%20different%20format%20like%26nbsp%3B4.5519E%2B18%20and%20when%20I%20try%20to%20convert%20it%20to%20normal%26nbsp%3Bnumbers%20using%20cell%20formatting%2C%20it%20changes%20the%20original%20number%20adding%20zeros%20to%20the%20end.%20Any%20suggestions%20or%20tips%20please%3F%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-174455%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-357360%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20txt%20to%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-357360%22%20slang%3D%22en-US%22%3ETry%20converting%20the%20txt%20file%20to%20excel%20using%203rd%20party%20converters%20and%20it%20will%20keep%20its%20original%20formatting%20of%20numbers.%20You%20can%20try%20Doc%20Converter%20for%20this%3A%20%3CA%20href%3D%22https%3A%2F%2Fwww.coolutils.com%2FTotalDocConverter%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.coolutils.com%2FTotalDocConverter%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-174656%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20txt%20to%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-174656%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much%20for%20your%20help.%20Now%20I%20understand%20why.%20I%20wasn't%20aware%20of%20the%20digit%20limit.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-174571%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20txt%20to%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-174571%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Gabriel%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExcel%20has%2015%20digit%20limit%20in%20representing%20the%20numbers%2C%20your%20ID%3As%20are%2019%20character%20long.%20The%20workaround%20not%20to%20convert%20such%20values%20to%20numbers%20but%20keep%20them%20as%20text.%3C%2FP%3E%0A%3CP%3EImporting%20your%20file%20don't%20directly%20load%20it%20into%20Excel%2C%20select%20Transform%20data%20first%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20871px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F30744i0287B32FE362E816%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIn%20opened%20query%20editor%20at%20right%20pane%20remove%20Changed%20Type%20step%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20468px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F30745iB5C6279497635210%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eresult%20will%20be%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20403px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F30746i26BFE2315E13B61D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EAfter%20that%20close%20the%20editor%20with%20loading%20table%20to%20Excel%20sheet.%20Please%20see%20attached%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-174550%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20txt%20to%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-174550%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergie%2C%20Thanks%20for%20the%20assitance.%20Here%C2%B4s%20the%20original%20file%20.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-174477%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20txt%20to%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-174477%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Gabriel%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20converted%20in%20another%20number%2C%20not%20format.%26nbsp%3B%3CSPAN%3E4551932613%20is%204.5519E%2B9%2C%20not%26nbsp%3B4.5519E%2B18.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ECould%20you%20please%20attach%20sample%20of%20your%20txt%20file%20with%20couple%20of%20records%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Gabriel Aguilar Stassano
New Contributor

Hello,

 

I´m trying to convert a txt file which has numbers to an excel file so I can review the information easily. I'm using excel 2016 so I go to data, get data, from txt file and the information pops up. The problem is that the numbers change. For example in the original txt a number could be 4551932613 and then it populates in a different format like 4.5519E+18 and when I try to convert it to normal numbers using cell formatting, it changes the original number adding zeros to the end. Any suggestions or tips please? Thanks

5 Replies

Hi Gabriel,

 

It converted in another number, not format. 4551932613 is 4.5519E+9, not 4.5519E+18.

 

Could you please attach sample of your txt file with couple of records?

Hi Sergie, Thanks for the assitance. Here´s the original file .

Solution

Hi Gabriel,

 

Excel has 15 digit limit in representing the numbers, your ID:s are 19 character long. The workaround not to convert such values to numbers but keep them as text.

Importing your file don't directly load it into Excel, select Transform data first

image.png

In opened query editor at right pane remove Changed Type step 

image.png

result will be like this

image.png

After that close the editor with loading table to Excel sheet. Please see attached

 

 

 

 

 

 

Thank you so much for your help. Now I understand why. I wasn't aware of the digit limit.
Try converting the txt file to excel using 3rd party converters and it will keep its original formatting of numbers. You can try Doc Converter for this: https://www.coolutils.com/TotalDocConverter
Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies