Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenThu, 12 Nov 2015 10:58:39 +0100Scientific Notation Without "E"https://ask.libreoffice.org/en/question/56459/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](https://ask.libreoffice.org/en/question/1124/scientific-notation-formatting-in-graphs-and-fields/), but its more than three years old and I hoped that something might have changed since then...Thu, 20 Aug 2015 11:51:33 +0200https://ask.libreoffice.org/en/question/56459/scientific-notation-without-e/Comment by finfly for <p>I'm trying to represent numbers in the form <code>x*10^y</code> instead of <code>xE+y</code> in LibreOffice Calc, but am not able to create a fitting custom format string.</p>
<p>The closest I got is this one: <code>#"*10^"E#</code>, which ends up with <code>x*10^Ey</code>.</p>
<p>Is there a way I can somehow hide the E without losing the correct numbers? Or any other way to achieve this formatting?</p>
<p>I'm aware of <a href="https://ask.libreoffice.org/en/question/1124/scientific-notation-formatting-in-graphs-and-fields/">this question</a>, but its more than three years old and I hoped that something might have changed since then...</p>
https://ask.libreoffice.org/en/question/56459/scientific-notation-without-e/?comment=60756#post-id-60756So there is no way to have axis labels in the form 10^-1? I'm also looking for this.Thu, 12 Nov 2015 10:58:39 +0100https://ask.libreoffice.org/en/question/56459/scientific-notation-without-e/?comment=60756#post-id-60756Answer by karolus for <p>I'm trying to represent numbers in the form <code>x*10^y</code> instead of <code>xE+y</code> in LibreOffice Calc, but am not able to create a fitting custom format string.</p>
<p>The closest I got is this one: <code>#"*10^"E#</code>, which ends up with <code>x*10^Ey</code>.</p>
<p>Is there a way I can somehow hide the E without losing the correct numbers? Or any other way to achieve this formatting?</p>
<p>I'm aware of <a href="https://ask.libreoffice.org/en/question/1124/scientific-notation-formatting-in-graphs-and-fields/">this question</a>, but its more than three years old and I hoped that something might have changed since then...</p>
https://ask.libreoffice.org/en/question/56459/scientific-notation-without-e/?answer=56463#post-id-56463You 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 NumberThu, 20 Aug 2015 13:06:46 +0200https://ask.libreoffice.org/en/question/56459/scientific-notation-without-e/?answer=56463#post-id-56463Comment by Cedric for <p>You cannot hide <code>E</code> from Formatcode because it triggers the split into factor and Exponent.</p>
<p>Maybe calculate <strong>Text</strong> output with Formula:</p>
<pre><code>=x/10^INT(LOG10(x)) & " * 10^" & INT(LOG10(x))
</code></pre>
<p>replace <code>x</code> by the Number respective the Celladdress of the Number</p>
https://ask.libreoffice.org/en/question/56459/scientific-notation-without-e/?comment=56508#post-id-56508Thanks - I had thought about something similar. However, since my numbers are labels on a diagram's axis, formulas don't work.Thu, 20 Aug 2015 23:55:18 +0200https://ask.libreoffice.org/en/question/56459/scientific-notation-without-e/?comment=56508#post-id-56508Answer by Lupp for <p>I'm trying to represent numbers in the form <code>x*10^y</code> instead of <code>xE+y</code> in LibreOffice Calc, but am not able to create a fitting custom format string.</p>
<p>The closest I got is this one: <code>#"*10^"E#</code>, which ends up with <code>x*10^Ey</code>.</p>
<p>Is there a way I can somehow hide the E without losing the correct numbers? Or any other way to achieve this formatting?</p>
<p>I'm aware of <a href="https://ask.libreoffice.org/en/question/1124/scientific-notation-formatting-in-graphs-and-fields/">this question</a>, but its more than three years old and I hoped that something might have changed since then...</p>
https://ask.libreoffice.org/en/question/56459/scientific-notation-without-e/?answer=56461#post-id-56461You 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"))
Thu, 20 Aug 2015 12:50:43 +0200https://ask.libreoffice.org/en/question/56459/scientific-notation-without-e/?answer=56461#post-id-56461Comment by karolus for <p>You won't like my answer which is a suggestion in fact: Simply don't do it. </p>
<p>Formally <code>x*10^y</code> 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 <code>x*EXP(LN(10)*y)</code>. 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 ...</p>
<p>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.</p>
<pre><code>=SUBSTITUTE(TEXT(A1;"0.00E0");"E";" x 10^")
</code></pre>
<p>for a value placed in A1 should do if your decimal delimiter is the full stop (otherwise ask again).</p>
<p>There is no way to set specific character formats for <strong>parts</strong> of a calculated text. You therefore cannot replace the "^" with setting the exponent to superscript.</p>
<p>Having placed the above given formula in B1 you can get back the numeric value by:</p>
<pre><code>=VALUE(SUBSTITUTE(B1;" x 10^";"E"))
</code></pre>
https://ask.libreoffice.org/en/question/56459/scientific-notation-without-e/?comment=56464#post-id-56464+1 for the comprehensive SUBSTITUTE-SolutionThu, 20 Aug 2015 13:14:40 +0200https://ask.libreoffice.org/en/question/56459/scientific-notation-without-e/?comment=56464#post-id-56464Comment by Cedric for <p>You won't like my answer which is a suggestion in fact: Simply don't do it. </p>
<p>Formally <code>x*10^y</code> 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 <code>x*EXP(LN(10)*y)</code>. 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 ...</p>
<p>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.</p>
<pre><code>=SUBSTITUTE(TEXT(A1;"0.00E0");"E";" x 10^")
</code></pre>
<p>for a value placed in A1 should do if your decimal delimiter is the full stop (otherwise ask again).</p>
<p>There is no way to set specific character formats for <strong>parts</strong> of a calculated text. You therefore cannot replace the "^" with setting the exponent to superscript.</p>
<p>Having placed the above given formula in B1 you can get back the numeric value by:</p>
<pre><code>=VALUE(SUBSTITUTE(B1;" x 10^";"E"))
</code></pre>
https://ask.libreoffice.org/en/question/56459/scientific-notation-without-e/?comment=56507#post-id-56507Hm, 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`.Thu, 20 Aug 2015 23:53:58 +0200https://ask.libreoffice.org/en/question/56459/scientific-notation-without-e/?comment=56507#post-id-56507Comment by Lupp for <p>You won't like my answer which is a suggestion in fact: Simply don't do it. </p>
<p>Formally <code>x*10^y</code> 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 <code>x*EXP(LN(10)*y)</code>. 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 ...</p>
<p>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.</p>
<pre><code>=SUBSTITUTE(TEXT(A1;"0.00E0");"E";" x 10^")
</code></pre>
<p>for a value placed in A1 should do if your decimal delimiter is the full stop (otherwise ask again).</p>
<p>There is no way to set specific character formats for <strong>parts</strong> of a calculated text. You therefore cannot replace the "^" with setting the exponent to superscript.</p>
<p>Having placed the above given formula in B1 you can get back the numeric value by:</p>
<pre><code>=VALUE(SUBSTITUTE(B1;" x 10^";"E"))
</code></pre>
https://ask.libreoffice.org/en/question/56459/scientific-notation-without-e/?comment=57000#post-id-57000Next we would expect number formats to support superscripts? Such requests tend to create a never ending series.Mon, 31 Aug 2015 17:30:41 +0200https://ask.libreoffice.org/en/question/56459/scientific-notation-without-e/?comment=57000#post-id-57000