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. Show
Chris W. Rea 10.7k15 gold badges75 silver badges95 bronze badges asked Sep 1, 2009 at 14:48
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 Other than that you are looking at some VBA to manage this, overkill I would have thought. answered Sep 2, 2009 at 9:37
1 I had success in solving what I think is a similar issue to yours. All I did was create a custom format of
Indrek 23.8k14 gold badges88 silver badges93 bronze badges answered May 16, 2012 at 17:31
small3687small3687 651 gold badge2 silver badges9 bronze badges 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. answered Jan 30, 2013 at 19:58
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" answered Sep 1, 2009 at 17:30
FoleyIsGoodFoleyIsGood 3402 silver badges6 bronze badges 2 Try this
Marko 3044 gold badges6 silver badges19 bronze badges answered Oct 1, 2012 at 15:11
1 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:
Change the "0" with the format you need. Voilà :) answered Nov 28, 2014 at 15:21
2 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 answered Mar 26, 2015 at 16:21
1 As
@FoleyIsGood commented: Instead of inputting it with "e" notation, write it out explicitly: answered Apr 3, 2016 at 1:43
4 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 answered Aug 3, 2016 at 15:35
1 I resolved the issue by using
Cas 1,9042 gold badges18 silver badges42 bronze badges answered Jan 10, 2018 at 10:17
random♦ 14.4k9 gold badges53 silver badges58 bronze badges answered Nov 29, 2013 at 17:09
What view shows how the worksheet will appear when printed?Page Layout view shows you the position of your worksheet as it will appear on a printed page. One of the features of Page Layout view is the Ruler which shows the size of the margins and allows you to drag the margins in or out to change the amount of the worksheet shown on the page.
Is a collection of formatting that specifies the fonts colors and graphical effects used throughout the workbook?A theme is a collection of formats for text, colors, images, and graphical effects applied throughout a workbook.
What is the process of changing a workbook's appearance by defining the font styles colors and decorative features?Formatting is the process of changing a workbook's appearance by defining fonts, styles, colors, and decorative features.
What indicates a manual page break?In the example below: An automatic page break is indicated by a dotted line. A manual page break is a dotted line labeled “Page Break”.
|