4

When I enter a number like 8230e12 into a Microsoft Excel 2000 cell, Excel changes the number I entered into 8230000000000000. (This is what I get when I press F2 to edit the cell's contents, not what Excel displays in the cell). How can I force Excel to keep the data in the format I typed it and still be able to format it and use it as a number? Displaying the cell in scientific notation is not enough, because the exponent is not the same one as the one I typed.

11 Answers 11

6

If you wish Excel to keep the cell formatted exactly as entered, i.e. 8230e12, regardless of whether you are looking at it or editing it, then this can't be done whilst retaining the ability to treat the cell contents as a number.

The only way I can see round this is to enter your data in cells that are formatted as text, and then have another cell, formatted as some kind of number, that has a formula of =VALUE(A1) or whatever so that calculations can be performed on this cell.

Other than that you are looking at some VBA to manage this, overkill I would have thought.

  • I also think this is the only option. The reason is, that Excel stores only the value and not exactly how you entered it. So if you enter 8,23e15 or 8235e12 or any other representation of the same number is not important for what Excel stores. When editing Excel uses the representation it thinks fits best - you have no control over that. – Turismo Sep 2 '09 at 11:30
1

I had success in solving what I think is a similar issue to yours. All I did was create a custom format of "'#" (note the apostrophe before the hash) and applied it to the column containing the numbers that needed to be viewed in their 12+ digit form. My values were still correct though as the columns were initially set to general.

1

I had this with list of long numbers, about 14000 entered into one column. I highlighted the column, Data -> Text to Columns -> Fixed length -> Don't create any break lines. Clear any that show up -> Select column data format text -> Finish. Worked like a charm.

0

Format the cell as custom number format and you can tweak the display to whatever you need. E.g. "0000E+12" will display your "8230e12" as "8230E+12"

  • As I wrote in the question, I'm not interested in the display, but on the way the data I entered gets reformatted as a formula. – Diomidis Spinellis Sep 2 '09 at 8:55
  • Sorry, I obviously misunderstood. If you enter '8230e12', Excel stores this as '8230000000000000'. Is this not the same value? You could enter '=8230*10^12' ?? – FoleyIsGood Sep 2 '09 at 9:47
0

Try this ="8230e12". It worked for me just now, after I saw that nothing you guys suggested was useful, gave this a shot.

  • 1
    Does this really still allow formatting and using the value as a number? – Daniel Beck Oct 1 '12 at 15:17
0

I had the same issue with a big number that I wanted without the exponent I solved it using Text function from Excel. in vba just do:

Application.WorksheetFunction.Text(Cells(myrow,mycol).Value(), "0")

Change the "0" with the format you need. Voilà :)

  • This changes a number's formatting, but does not retain the format entered. – Diomidis Spinellis Nov 29 '14 at 16:21
  • Ha sorry I read it the other way around – TecHunter Nov 29 '14 at 17:01
0

This Works: 1. Highlight the column 2. Right click "Format Cells..." 3. From the number Tab, Select Category=Number and ensure Decimal Place is set to Zero (unless you have decimal values in the cell content) 4. OK

  • does not solve the problem... Not an answer – Prasanna Mar 26 '15 at 17:47
0

As @FoleyIsGood commented: Instead of inputting it with "e" notation, write it out explicitly: = 8230 * 10^12.

  • Somebody suggest this 4 years ago..... – Ramhound Apr 3 '16 at 1:44
  • Oh, I didn't see @FoleyIsGood's suggestion because it was just a comment, not an answer. But kudos to him / her for having the same idea. Anyway I'm leaving this up because it deserves to be an answer, not just a comment. – Steve Byrnes Apr 3 '16 at 1:58
  • This duplicates another answer and adds no new content. Please don't post an answer unless you actually have something new to contribute. – DavidPostill Apr 3 '16 at 8:59
  • @DavidPostill I feel like I'm going nuts. What "answer" did I duplicate? I've read through them ten times now. I only see this in a comment, not an answer. – Steve Byrnes Apr 3 '16 at 10:18
0

Put a " ' " before the number, then add a helper column that does Right(cell you typed in,len(cell you typed in). This will put it back in full

  • This is essentially the same as the accepted answer, just a different way to make it text. Minor variations of an existing answer are more appropriate as a comment, which requires a little more rep. You could also propose it as an edit to expand that answer. – fixer1234 Aug 3 '16 at 17:07
0

I resolved the issue by using =IF(MID(B31,6,1)="E","Y","N") to identify where the Exponential lies (represented by the "E") and then replacing the E with "#" using =REPLACE(B31,6,1,"#").

-1
  1. Highlight the range of cells or column
  2. Click Custom format
  3. Look for question marks like: "??/??"
  4. Click Enter

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.