356810115212.947 is display as 3.5681E+11 in Excel. I want it to be display as 0.3568E+12, is there a way to do it?
3 Answers
Right-click the cell and select "Format Cells...".
Set the Category to "Custom" and enter the following as format : .0000E+00, then click OK.
For your example of 356810115212.947, I get :
For "engineering correct" display of numbers with exponents,
i.e. to align with prefixes (exponent divisible by 3),
you might wish to use ###.0E+00 as custom/user formatting;
this isn't what was asked for though, as the above will become 356.8E+09 i.e. "356 T"
="0."&IF(A1<10000,A1,LEFT(LEFT(A1,5)+5,4))&"E+"&LEN(INT(A1)) should work just fine
idea : manually concatenate all required component to make up the not-so scientific exponent notation. +5 is to cater for round up .
catch : works on positive, >1, numbers only. need to work out a different (but similar) one for other scale. Then can us if to 'combine' it as a formula.
