2

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.

  • 1
    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?. – DavidPostill Nov 6 '16 at 12:25
  • @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. – chthonicdaemon Nov 6 '16 at 12:42
4

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)

enter image description here

  1. 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

enter image description here

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

enter image description here

  • 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. – Stenemo Dec 4 '18 at 10:24
-1
Sub scientificnotation()
    ' put in general form
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = "([0-9.]@)E([-+0-9]@)([!0-9])"
        .Replacement.Text = "\1##×10##\2##\3"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchAllWordForms = False
        .MatchSoundsLike = False
        .MatchWildcards = True
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    ' take out leading 0 exponents
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = "##+0"
        .Replacement.Text = "##+"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = True
        .MatchCase = False
        .MatchWholeWord = False
        .MatchAllWordForms = False
        .MatchSoundsLike = False
        .MatchWildcards = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    ' take out + exponents
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = "##+"
        .Replacement.Text = "##"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = True
        .MatchCase = False
        .MatchWholeWord = False
        .MatchAllWordForms = False
        .MatchSoundsLike = False
        .MatchWildcards = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
  ' take out leading 0 exponents for negative numbers
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = "##-0"
        .Replacement.Text = "##-"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = True
        .MatchCase = False
        .MatchWholeWord = False
        .MatchAllWordForms = False
        .MatchSoundsLike = False
        .MatchWildcards = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    ' free up ×10
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = "##×10##"
        .Replacement.Text = "×10##"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = True
        .MatchCase = False
        .MatchWholeWord = False
        .MatchAllWordForms = False
        .MatchSoundsLike = False
        .MatchWildcards = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    ' elevate exponents
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find.Replacement.Font
        .Superscript = True
        .Subscript = False
    End With
    With Selection.Find
        .Text = "##([-+0-9]@)##"
        .Replacement.Text = "\1"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = True
        .MatchCase = False
        .MatchWholeWord = False
        .MatchAllWordForms = False
        .MatchSoundsLike = False
        .MatchWildcards = True
    End With
    Selection.Find.Execute Replace:=wdReplaceAll

End Sub

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.