CODEWITHSUNDEEP
excel-formula
Marla
Marla

Reputation: 340

Format scientific notation in Excel

Is it possible to format numbers in scientific notation as exponents with the base 10 rather than E in Excel? For example, Excel converts 0.00123 to 1.23E3, but I would like the format 1.23x10^3

Upvotes: 2

Views: 3694

Answers (4)

Walter Eberle
Walter Eberle

Reputation: 1

I improved the previous answer from Peter K. Now it includes the minus sign on the power, auto-setting of 1 to 3 digits for the power, and also zero power value for integer numbers.

It works when ";" is the Excel list and function parameters separator. Replace ";" by "," if required, based on your own Excel configuration.

I use the LET function to better understand the formula. It allows us to create a list of [names; values/formula] pairs.

Numeric value to convert is set at A1 cell. `

=LET(valor;Q12;
template;"0,00E+000";
nro;TEXT(valor;template);
base;LEFT(nro;FIND("E";nro)-1);
signoExp;IF(LEFT(RIGHT(TEXT(valor;template);4);1)="-";UNICHAR(8315);"");
powD1;NUMBERVALUE(LEFT(RIGHT(TEXT(valor;template);3);1));
powD2;NUMBERVALUE(LEFT(RIGHT(TEXT(valor;template);2);1));
powD3;NUMBERVALUE(RIGHT(TEXT(valor;template);1));
superDigits;{8304;185;178;179;8308;8309;8310;8311;8312;8313};
expS1;UNICHAR(INDEX(superDigits;powD1+1;1));
expS2;UNICHAR(INDEX(superDigits;powD2+1;1));
expS3;UNICHAR(INDEX(superDigits;powD3+1;1));
IF(valor<>"";base&"x10"&signoExp&
RIGHT(expS1&expS2&expS3;LEN(powD1*100+powD2*10+powD3));""))

`

https://ucao365-my.sharepoint.com/:i:/g/personal/waltereberle_uca_edu_ar/EblCkDyPxtpMjD3Pur8rzWkBACfeiQYlUAbNMjgFU9KFoQ?e=3HyfoE

Upvotes: 0

user1347148
user1347148

Reputation: 11

Building on Peter K's reply... If you put this formula in B1:

=TEXT(A1,"0.00#E+0")

And the below formula in C1 then it will handle negative exponents:

=SUBSTITUTE(LEFT(TEXT(A1,"0.00E+00"),5),"E","")&
"x10"&
IF(COUNTIF(B1,"*E-*"),UNICHAR(8315),"")&
UNICHAR(INDEX({8203,185,178,179,8308,8309,8310,8311,8312,8313},1,NUMBERVALUE(LEFT(RIGHT(TEXT(A1,"0.00E+00"),2),1)+1)))&
UNICHAR(INDEX({8304,185,178,179,8308,8309,8310,8311,8312,8313},1,NUMBERVALUE(RIGHT(TEXT(A1,"0.00E+00"),1))+1))

Further, if you want three digit precision after the decimal instead of two then in the first line change this portion from LEFT(TEXT(A1,"0.00E+00"),5) to LEFT(TEXT(A1,"0.000E+00"),6).

Upvotes: 1

hisakatha
hisakatha

Reputation: 29

You may want to use VBA (Visual Basic for Applications).

I created a VBA macro (https://github.com/hisakatha/exp2superscript) to convert exponents into superscripts via text formatting function. This macro can be imported in the Excel's Visual Basic for Applications editor and registered in Excel Add-ins, and then the macro can be called in Excel.

Briefly, the macro replaces "E" with "×10" using the VBA Replace function and format the exponent part of numbers as superscripts using an assignment: <cell>.Characters(Start:=<index of the exponent>).Font.Superscript = True

Upvotes: 0

Peter K.
Peter K.

Reputation: 960

This is not possible by using the standard number formatting in Excel.

You could of course convert your number to a text string with =TEXT(A1;"0.00E+00"), and then you can convert this text string to a new text string in the format that you want. It gets a bit tricky with the superscripts, as you have to display these with the UNICHAR function (this function is available since Excel 2013).

The Unicode values to use with the function are the following (from superscript 0 till 9): 8304,185,178,179,8308,8309,8310,8311,8312,8313

So superscript 50 would be =UNICHAR(8309)&UNICHAR(8304)

You can create elsewhere in your workbook a named range with this values, so the conversion would be easy with an INDEX. Actually, I would create two ranges, one for the first digit of the exponent (where you don't have a 0, but an empty string, as you don't want to display 10^03 but 10^3), and the second one for the second digit of the exponent (where you keep the 0).

To summarize:

  • Convert the number to a text sting with =TEXT(A1;"0.00E+00")
  • Replace the "E" with "x"
  • Replace the "+" with "10"
  • If number is negative, add a -
  • Replace the two last characters with the corresponding superscript (convert back to number with NUMBERVALUE() and then use INDEX() to get the Unicode value to feed into the UNICHAR function.

So for the last digit, the formula is (the named range for the Unicode values is EXPO1 and EXPO2 in my case):
=UNICHAR(INDEX(EXPO2;1;NUMBERVALUE(RIGHT(A1;1))+1))
For the first digit of the exponent (i.e. the one but last character of the text string), use this formula to convert into superscript:
=UNICHAR(INDEX(EXPO1;1;NUMBERVALUE(LEFT(RIGHT(A1;2);1)+1)))

I am a bit lazy here, I could use MID as well instead of the LEFT/RIGHT combination. And please note that the named range is now EXPO1, where the first value is the Unicode value of the empty string (8203).

The entire formula is:
=LEFT(TEXT(A1;"0.00E+00");4)& "x10"& UNICHAR(INDEX(EXPO1;1;NUMBERVALUE(LEFT(RIGHT(TEXT(A1;"0.00E+00");2);1)+1)))& UNICHAR(INDEX(EXPO2;1;NUMBERVALUE(RIGHT(TEXT(A1;"0.00E+00");1))+1))

with EXPO1 and EXPO2 the named ranges as explained above.

To make the formula completely self-sustained, you can hardcode these into the formula, so this becomes then :
=LEFT(TEXT(A1;"0.00E+00");4)& "x10"& UNICHAR(INDEX({8203,185,178,179,8308,8309,8310,8311,8312,8313};1;NUMBERVALUE(LEFT(RIGHT(TEXT(A1;"0.00E+00");2);1)+1)))& UNICHAR(INDEX({8304,185,178,179,8308,8309,8310,8311,8312,8313};1;NUMBERVALUE(RIGHT(TEXT(A1;"0.00E+00");1))+1))

Finally, please note that the formula is dependent on how you convert the number into text. Per OP initial question, I used 2 digits precision after the comma ("0.00E+00"). If you want to display more, you have to extract more than 4 characters with the first LEFT formula in my example.

Upvotes: 2

Related Questions