Auto-format Excel scientific notation in Word

Typically when you copy and paste data from Excel to Word you get the ugly scientific notation that Excel uses, of the kind 3.23E+03 or 6.29E-07, which are hard to read, at least if you have been thaught that it should really be written like 3.23×103.

Re-formatting by hand is tedious and can be really time consuming of you have many entries. Therefore I created a macro that will automatically look for all #.##E+-# and replace them with the more pleasantly looking #.##×10# . You can copy the below code and insert in the VBA editor as a new macro.

Sub scientificnotation()
    ' put in general form
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = "([0-9.]@)E([-+0-9]@)([0-9]{2;3})"
        .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

This macro can also be installed through the macro enable Word template below. Just download and put in the auto load folder for Word.

 

Lämna ett svar

E-postadressen publiceras inte. Obligatoriska fält är märkta *

This site uses Akismet to reduce spam. Learn how your comment data is processed.