Ask Your Question

Scientific Notation Without "E"

asked 2015-08-20 11:51:33 +0200

Cedric gravatar image

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 flag offensive close merge delete


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

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

2 Answers

Sort by » oldest newest most voted

answered 2015-08-20 12:50:43 +0200

Lupp gravatar image

updated 2015-08-20 12:58:10 +0200

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"))
edit flag offensive delete link more


+1 for the comprehensive SUBSTITUTE-Solution

karolus gravatar imagekarolus ( 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.

Cedric gravatar imageCedric ( 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.

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

answered 2015-08-20 13:06:46 +0200

karolus gravatar image

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

edit flag offensive delete link more


Thanks - I had thought about something similar. However, since my numbers are labels on a diagram's axis, formulas don't work.

Cedric gravatar imageCedric ( 2015-08-20 23:55:18 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-08-20 11:51:33 +0200

Seen: 1,815 times

Last updated: Aug 20 '15