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…
So there is no way to have axis labels in the form 10^-1? I’m also looking for this.
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"))+1 for the comprehensive SUBSTITUTE-Solution
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.
Next we would expect number formats to support superscripts? Such requests tend to create a never ending series.
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
Thanks - I had thought about something similar. However, since my numbers are labels on a diagram’s axis, formulas don’t work.