Scientific notation in Microsoft Word

Question

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.

Solution

You can use find and replace in Word. You'll need to use a two step approach:

1. copy numbers from excel in default format (1.234E+56)

2. identify numbers with scientific notation:
   • press CTRL+H, more and select "use wildcards"
   • in "find what" enter ([0-9.]@)E([-+0-9]@)([!0-9])
   • in "replace with" enter \1##\2##\3
     (or use any other special characters which don't occur in your documents)
   • press replace all

3. make superscript:
   • "find what": ##([-+0-9]@)##
   • "replace to": \1
   • press format - font, select superscript

Comments

[DavidPostill]

Please note that superuser.com is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?.

[chthonicdaemon]

@DavidPostill I've added the closest method I've been able to find, but I literally don't even know the first thing about automating Word in this way. I'm used to a "text-based" paradigm, where my Python scripts write to text files, typeset by Python. I was hoping this relatively common thing from the LaTeX word had a solution I had just been unable to find.

[Stenemo]

This is a good solution that works well if you only need to do it once in one document that has many occurrences. It would be great if someone could also add code for creating a macro using this solution.