When doing computer calculation, scientific notation is mostly represented as 1.23e-12. In scientific writing, this is frowned upon and we use explicit multiplication and superscripts for powers like 1.23×10⁻¹². This leads to a barrier between for instance Excel and Word where we can no longer simply copy-paste or even better embed the results. This in turn leads to avoidable typing errors in our documents.
Is there a way of automating this process? In LaTeX for instance, there are a number of packages (for example siunitx) which define macros which automate at least the conversion, so you can type \num{1.23e-12} to get the nicely typeset result. I would ideally like to avoid all manual steps to get from the number in an Excel spreadsheet to the typeset version of the same number in a Word document, something like defining \renewcommand{\myimportantresult}{\num{1.23e-12}} in LaTeX and then just using the macro everywhere instead of the number.
I have found this method to reformat scientific notation, which boils down to using the following formula (in A2 to convert A1):
=LEFT(TEXT(A1;"0.00E+0");3) & "×10^" & RIGHT(TEXT(A1;"0.00E+0");3)
This is close and results in 1.2×10^-12, but I don't know how to add superscripts to only parts of a cell which is calculated by a formula. Furthermore, it complicates the spreadsheet by having these "extra" cells just for display.
As an added piece of difficulty, the method I tried to link a value in Excel to Word (paste link) does not seem to work in the Mac version of office 2016 that I am using.
Best Answer
You can use find and replace in Word. You'll need to use a two step approach:
1.234E+56)([0-9.]@)E([-+0-9]@)([!0-9])\1##\2##\3(or use any other special characters which don't occur in your documents)
##([-+0-9]@)##\1