+ Reply to Thread
Results 1 to 20 of 20

Why excel forces this weird cell formatting?

  1. #1
    Registered User
    Join Date
    03-21-2007
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    73

    Question 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:

    Please Login or Register  to view this content.
    Thanks!
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by micko_escalade; 04-09-2016 at 12:22 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Why excel forces this weird cell formatting?

    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

  3. #3
    Registered User
    Join Date
    03-21-2007
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    73

    Re: Why excel forces this weird cell formatting?

    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?

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Why excel forces this weird cell formatting?

    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

  5. #5
    Registered User
    Join Date
    03-21-2007
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    73

    Re: Why excel forces this weird cell formatting?

    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

  6. #6
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Why excel forces this weird cell formatting?

    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.

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Why excel forces this weird cell formatting?

    What is getting messed up? It looks fine here
    Capture CSV.JPG
    Column C is as it is in the CSV file.

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Why excel forces this weird cell 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

  9. #9
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Why excel forces this weird cell formatting?

    Quote Originally Posted by newdoverman View Post
    What is getting messed up? It looks fine here
    Attachment 455208
    Column C is as it is in the CSV file.
    I just noticed...

    ...is B2 not matching the input value = 14065313439 ???

  10. #10
    Registered User
    Join Date
    03-21-2007
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    73

    Re: Why excel forces this weird cell formatting?

    Quote Originally Posted by ben_hensel View Post
    I think you will probably want to import it as a text string, not a number, to retain that leading zero:

    Attachment 455207
    Nice!
    What did you select in steps 1 and 2?
    All I get is this:
    Attached Images Attached Images

  11. #11
    Registered User
    Join Date
    03-21-2007
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    73

    Re: Why excel forces this weird cell formatting?

    Quote Originally Posted by ChemistB View Post
    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
    Its a international number that I don't want to reveal here. When dialing out I have to use 011 sort of +country code

  12. #12
    Registered User
    Join Date
    03-21-2007
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    73

    Re: Why excel forces this weird cell formatting?

    Quote Originally Posted by ben_hensel View Post
    I just noticed...

    ...is B2 not matching the input value = 14065313439 ???
    Value does not matter. I just need correct formatting.

  13. #13
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Why excel forces this weird cell formatting?

    Quote Originally Posted by micko_escalade View Post
    What did you select in steps 1 and 2?
    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.

  14. #14
    Registered User
    Join Date
    03-21-2007
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    73

    Re: Why excel forces this weird cell formatting?

    Quote Originally Posted by ben_hensel View Post
    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

  15. #15
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Why excel forces this weird cell formatting?

    In step 3 you have to select each of the columns and then set their format to "Text" with the radio button.

  16. #16
    Registered User
    Join Date
    03-21-2007
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    73

    Re: Why excel forces this weird cell formatting?

    Quote Originally Posted by ben_hensel View Post
    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.

  17. #17
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Why excel forces this weird cell formatting?

    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
    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    Date/Time Calling # Called # Duration Amount Tax Total Destination Extension
    2
    4/6/2016 10:32 13062312439 011389512123123 4 min

  18. #18
    Registered User
    Join Date
    03-21-2007
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    73

    Re: Why excel forces this weird cell formatting?

    Quote Originally Posted by newdoverman View Post
    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
    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    Date/Time Calling # Called # Duration Amount Tax Total Destination Extension
    2
    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?

  19. #19
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Why excel forces this weird cell formatting?

    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.

  20. #20
    Registered User
    Join Date
    03-21-2007
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    73

    Re: Why excel forces this weird cell formatting?

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] lookups in excel with weird formatting
    By ammartino44 in forum Excel General
    Replies: 5
    Last Post: 05-21-2015, 01:17 PM
  2. Using EXCEL to calculate forces in wedges
    By RyanFET in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-04-2015, 03:27 PM
  3. Replies: 3
    Last Post: 09-03-2013, 11:27 AM
  4. Filling a series of formulas with weird cell formatting
    By eshortt84 in forum Excel General
    Replies: 3
    Last Post: 08-08-2013, 02:28 PM
  5. Conditional Formatting Forces Ugly Font
    By Alcon in forum Excel General
    Replies: 4
    Last Post: 03-02-2012, 04:40 PM
  6. Laptop + Win 7 + Excel = Weird formatting?
    By j1burn in forum Excel General
    Replies: 4
    Last Post: 07-04-2010, 05:34 PM
  7. [SOLVED] Weird Excel date formatting problem
    By Ian D in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 10-21-2005, 11:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1