Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Removing Scientific Notation in Excel cell

This is a discussion on Removing Scientific Notation in Excel cell within the Excel Questions forums, part of the Question Forums category; Ladies and Gentleman, I have tried to serach this forum but could not find what I need. I have bunch ...

  1. #1
    Board Regular
    Join Date
    Feb 2007
    Posts
    354

    Default Removing Scientific Notation in Excel cell

    Ladies and Gentleman,

    I have tried to serach this forum but could not find what I need. I have bunch of 22 digits numbers, e.g. 9612019086568871420292. When I copy them into Excel cells they become like 9.61202E+21. And, this is the problem. I would like to see them as 9612019086568871420292. I have tried all possible way to format as General, Numbers, Texts, etc but none worked. Also did Paste Special>Value, etc. none worked. Any suggestion? Thank you in advance.

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,048

    Default

    Format the cells as text before you paste into them.
    If posting code please use code tags.

  3. #3
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,255

    Default

    Alternatively, you can precede your number with an apostrophe ' ...

    '9612019086568871420292

    Hope this helps!

  4. #4
    Board Regular
    Join Date
    Feb 2007
    Posts
    354

    Default

    Thank you both for your reply but the problem here is I already have data in Scientific notation and I tried to format another entire col. to text and I copied and pasted data to that Col..and it is not working.

  5. #5
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,659

    Default

    Hi sjha

    You have to go back to the source and copy the numbers again.

    The cell value has only 15 significant digits and so when you copy 612019086568871420292 to excel you lose the last digits and get 9612019086568870000000.

    This is independent from displaying it in scientific notation or just as an integer. Those digits were lost during import.

    You have to import the numbers again, this time as text.

    Hope this helps
    PGC

  6. #6
    Board Regular
    Join Date
    Feb 2007
    Posts
    354

    Default

    Thank you all.

  7. #7
    New Member
    Join Date
    Jun 2008
    Posts
    1

    Question Re: Removing Scientific Notation in Excel cell

    I know this topic is old, but its at the top of google for this topic. Disabling scientific notation in excel.

    This is what I did:

    in the column with your large numbers add the blank character " " by pressing ALT + 0160. This will make excel think that everything after that character is text and treat it accordingly.

    An easy way to do this with a large amount of numbers:

    Make a column to the left of your number column

    column1 | numbers
    --------------------
    alt + 0160 | 33939939393939

    fill column 1 with blank characters (ALT + 0160) all the way down

    copy the two columns into notepad, click edit and replace all of the large space with nothing which should leave you with the blank character + your number with no spaces.

    This has annoyed me for years and I finally figured out a decent solution that doesn't make my data look ghetto.

    Farewell.

  8. #8
    New Member
    Join Date
    Aug 2010
    Posts
    1

    Default Re: Removing Scientific Notation in Excel cell

    I have a Similar specific problem where this occurs daily.

    I export mail data (mailing lists) into CSV files every day, and the barcode data gets generated from this string of 12 numbers.

    The problem is, every time I open the excel file to make a change (like, an address contains an extra space, or there's some other minor formatting issue with the text), Excel changes the column of numbers (for the barcode) into scientific notation. If I then attempt to force the column to show up as numeric or even as "special" formatting, the number gets rounded and I lose the last couple of digits. When/if I save the file as csv, the data stays in that format instead of in the 12-number format, and the datafile is then useless. Which causes me to swear mightily at my computer in futile rage, hurling curses that cause the paint on the walls to peel and my co-workers to cover their ears.

    It would be REALLY great if there were a way to disable the "automatic" Excel scientific notation conversion. T

    he apostrophe trick (or the "blank char" trick) doesn't work, since that gets exported in the CSV data as well, which screws up the barcode. Then the mail doesnt get delivered

    So... there's REALLY no way to disable the automatic scientific notation "feature?" -- Leebs
    Last edited by LeberMac; Aug 12th, 2010 at 03:11 PM. Reason: formatting

  9. #9
    New Member
    Join Date
    Dec 2010
    Posts
    2

    Smile Re: Removing Scientific Notation in Excel cell

    The goal of course is to preserve the entire # and not allow Excel to lose anything in translation by converting a # to scientific notation, correct? In that case, highlight the cell/column with your large #, right-click to format the cell(s), and choose the custom format from the Number tab. In the Type box type in one lb symbol (#) and that's it. Now save as .csv and the full # is preserved. On pulling the file back into Excel, reformat the cell/column back to custom/#, accordingly.

  10. #10
    New Member
    Join Date
    Dec 2010
    Posts
    2

    Default Re: Removing Scientific Notation in Excel cell

    Of course all of this comes 3 1/2 years too late, but what you can do is is copy the #s, paste them to WordPad first, format the receiving cells inside Excel for text, recopy the list from WordPad, and then paste them to the text-formatted cells inside Excel. This should work.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com