Why excel forces this weird cell formatting?
Hi all,
I've spent lot of time trying to figure out this issue but no luck.
Basically if I open .csv file in notepad called number column has correct format displayed "0113......" but in Excel 2013 it has something like "1.13895E+12" why is that?
Notepad data:
Thanks!
Date/Time,Calling #,Called #,Duration,Amount,Tax,Total,Destination,Extension 04/06/2016 10:32:29,14065313439,011389512123123,4 min
Last edited by micko_escalade; 04-09-2016 at 12:22 PM.
excel is retying to interpret that as a number - a very big number, and is formatting it in scientific notation.
Try pulling it in as text
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Ok, I see.
For .csv files is Excel is the default application and I opened it by double clicking. I've looked around and nowhere I can see import as text option. Can you please advise?
In Excel, click on the Data tab, Get External Data, From Text. Choose the file, IMPORT, Delimited then Choose comma as the delimiter and follow the prompts.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
I feel so stupid. How could I miss it
When I import it like that it messes up columns. Is there any other option that will tell excel not to format it at all?
I've tried all of other formatting options but none will give me plain number 011389512123123
I think you will probably want to import it as a text string, not a number, to retain that leading zero:
import.png
Click the [* Add Reputation] Button to thank people for help!
Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.
Nice!Originally Posted by ben_hensel
What did you select in steps 1 and 2?
All I get is this:
I actually had it open in a text viewer like NOTEPAD, then copy/pasted into Excel, and then used the Convert-Text-To-Columns wizard (Data Ribbon, Data Tools Group, "Text to Columns" button on the right). I think the only thing I changed from defaults was selecting the comma as a deliminator in step 2.
Ok, thanks! I was able to follow all of the steps you provided but when I click finish at the end it still formats it as scientific notation. Did you got plain number? 011389512123123
What is getting messed up? It looks fine here
Capture CSV.JPG
Column C is as it is in the CSV file.
I just noticed...
...is B2 not matching the input value = 14065313439 ???
Value does not matter. I just need correct formatting.
0113...... is not a number. Numbers do not begin with 0 unless it's <1 like 0.123
When you are in the Import window, try different combinations of delimiters to try to get your columns right, like spaces or commas
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Its a international number that I don't want to reveal here. When dialing out I have to use 011 sort of +country code
In step 3 you have to select each of the columns and then set their format to "Text" with the radio button.
Ok, that worked, now what I miss is "0" in front of 11
I did paste from Notepad as plain text but Excel probably strips is "0" before it converts into scientific notation.
In Excel, click on the Data tab, Get External Data, From Text. Choose the file, IMPORT, Delimited then Choose comma as the delimiter, select each column and format as TEXT then click FINISH.
My actual results
Date/Time Calling # Called # Duration Amount Tax Total Destination Extension 4/6/2016 10:32 13062312439 011389512123123 4 min
Thanks for the detailed explanation!
I did exactly as you did. Here's the video http://screencast-o-matic.com/watch/cDfQou1zrN
What am I missing?
I watched your video. What you are missing is that you didn't select each column and click on text. You selected the first one and selected text but omitted doing that for each column.
Yup, that's what it was!
Here's video http://screencast-o-matic.com/watch/cDfQop1z3X in case someone has same issue and how to fix it.
Thank you all!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks