# Scientific Notation Without "E"

I'm trying to represent numbers in the form x*10^y instead of xE+y in LibreOffice Calc, but am not able to create a fitting custom format string.

The closest I got is this one: #"*10^"E#, which ends up with x*10^Ey.

Is there a way I can somehow hide the E without losing the correct numbers? Or any other way to achieve this formatting?

I'm aware of this question, but its more than three years old and I hoped that something might have changed since then...

edit retag close merge delete

So there is no way to have axis labels in the form 10^-1? I'm also looking for this.

( 2015-11-12 10:58:39 +0200 )edit

Sort by » oldest newest most voted

You won't like my answer which is a suggestion in fact: Simply don't do it.

Formally x*10^y is an expression using two operators (in a specific order of preference). In addition the pattern is not limited to integers in the position of y, but is thought to be equivalent to x*EXP(LN(10)*y). You also will always need to use the "E-notation" when entering or editing numbers with an "order-of-magnitude-part". Despite the fact that the "scientific" notation seems not to be internationally specified (by ISO, e.g.) the E-notation is a de-facto standard and can also be used for output/input processes. If you once want to use complex numbers (which are actually represented as text) these will be bound to the E-notation ...

Preparing your numerics for prettyprint or for export to a publishing tool, you will have to explicitly convert them into text. You should, however, use the "x" then instead of the asterisk to distinguish the number formally from expressions.

=SUBSTITUTE(TEXT(A1;"0.00E0");"E";" x 10^")


for a value placed in A1 should do if your decimal delimiter is the full stop (otherwise ask again).

There is no way to set specific character formats for parts of a calculated text. You therefore cannot replace the "^" with setting the exponent to superscript.

Having placed the above given formula in B1 you can get back the numeric value by:

=VALUE(SUBSTITUTE(B1;" x 10^";"E"))

more

+1 for the comprehensive SUBSTITUTE-Solution

( 2015-08-20 13:14:40 +0200 )edit

Hm, thanks. I think it would have made sense in my case because it was for diagram axis labels which only consisted of numbers of the form 10^x.

( 2015-08-20 23:53:58 +0200 )edit

Next we would expect number formats to support superscripts? Such requests tend to create a never ending series.

( 2015-08-31 17:30:41 +0200 )edit

You cannot hide E from Formatcode because it triggers the split into factor and Exponent.

Maybe calculate Text output with Formula:

=x/10^INT(LOG10(x)) & " * 10^" & INT(LOG10(x))


replace x by the Number respective the Celladdress of the Number

more