Aaron Nimocks 19 Posted December 24, 2009 Report Share Posted December 24, 2009 I am trying to enter numbers like this (3603840000536) into a CSV. As usual I get the E+12 crap and it doesn't show it all. But when I format cells to a number I (3603840000000). Seems it cuts off the end numbers. How do I do this? Everything i can find on the internet says to save as txt first then convert in excel. Quote Link to post Share on other sites
sweetman 1 Posted December 24, 2009 Report Share Posted December 24, 2009 I am trying to enter numbers like this (3603840000536) into a CSV. As usual I get the E+12 crap and it doesn't show it all. But when I format cells to a number I (3603840000000). Seems it cuts off the end numbers. How do I do this? Everything i can find on the internet says to save as txt first then convert in excel. Hi Aaron, There is no way to stop Excel from doing this on a CSV, BUT there is a workaround. Change the extension of the file to .dat and then open with excel. Open the .dat file in Excel and it will then ask you how to import the data. Click Next, check only the comma box click next, click text radio button, click finish. There you go your whole number in a box without scientific notation. Hope that helps! B Quote Link to post Share on other sites
Aaron Nimocks 19 Posted December 24, 2009 Author Report Share Posted December 24, 2009 Hi Aaron, There is no way to stop Excel from doing this on a CSV, BUT there is a workaround. Change the extension of the file to .dat and then open with excel. Open the .dat file in Excel and it will then ask you how to import the data. Click Next, check only the comma box click next, click text radio button, click finish. There you go your whole number in a box without scientific notation. Hope that helps! B Ive tried that way and 50 other similar ways. Still can't get it to work for some reason. No idea what I am doing wrong. Quote Link to post Share on other sites
sweetman 1 Posted December 24, 2009 Report Share Posted December 24, 2009 Ive tried that way and 50 other similar ways. Still can't get it to work for some reason. No idea what I am doing wrong. That is odd, I just tested again and that works for me. Usually the only reason it does it is because it automatically assumes that CSV input with all numbers is a number. If you get it to understand that the field is text prior to it actually loading the file it should not default to scientific notation. One thing you may not be doing... Rename file to extension DAT.Go to excel File>Open file.datFirst window comes up make sure Delimited radio button is checked.Click NextUncheck the TAB boxClick the Comma BoxThis will divide the fields up in the Data Preview WindowClick NextNow in the data preview your columns will be there with General Above them*Click the header above the column with your long number in it*Click the TEXT radio buttonYou have to repeat the * steps above for each field with a long number in themOnce you are done with that click Finish. Please tell me that worked. Quote Link to post Share on other sites
Aaron Nimocks 19 Posted December 24, 2009 Author Report Share Posted December 24, 2009 Damn, finally figured it out, now I feel stupid. What I was doing was using the same file to keep trying this on. Well when first attempt failed the data in the text file was actually change to the E+12 crap instead of the number. So no wonder why it wasn't converting right! Because the 15 digit number no longer existed. Made a fresh output and saved as .txt then imported and worked like a champ. Thanks for trying to help though. Quote Link to post Share on other sites
sweetman 1 Posted December 24, 2009 Report Share Posted December 24, 2009 No problem let me know if you need anything in excel I am pretty good an manipulating all kinds of data there. Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.