Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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. 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 ISI, 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 numebr formally from expressions.

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

for a value placed in A1 should do ikf 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"))

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. 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 ISI, 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 however, use the "x" then instead of the asterisk to distinguish the numebr number formally from expressions.

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

for a value placed in A1 should do ikf 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"))

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. 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"))