Removing Scientific Notation in Excel cell

sjha

Active Member
Joined
Feb 15, 2007
Messages
355
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.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Format the cells as text before you paste into them.
 
Upvote 0
Alternatively, you can precede your number with an apostrophe ' ...

'9612019086568871420292

Hope this helps!
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.:LOL:
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top