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 taught 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.
[code]
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
[/code]This macro can also be installed through the macro enable Word template below. Just download and put in the auto load folder for Word.
