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.

 

Simulating greywater reuse in a residential building

The last few weeks I’ve been coding a simulator for greywater reuse scenarios based on real water usage data from HSB Living Lab. The input data is disaggregated hot and cold water consumption, with a time resolution of 10 minutes.

Simulated tank levels in the greywater treatment system

The simulation code allows the researcher to define from what micro-use points greywater is to be collected, and for what purposes it should be reused. The simulation of the treatment system itself is rather archaic, but the output is still kind of cool. The graph above show the levels in the untreated and treated water tanks over a period of 8 days. Tank volumes are 0.3 cbm and the treatment capacity in this run was set to 2 liters per minute, while collecting from sink and shower and reusing for sink, shower and WC. This scenario typically results in 50-60% reduction in total mains hot water consumption and 20-30% total mains cold water consumption.

Another cool feature of the simulator is the possibility to incorporate rain harvesting from real precipitation data into the equation. In the Hadley-cell zone wich defines Gothenburg this gives some pretty interesting numbers.

The results from this simulation should appear in a journal paper “shortly” (meaning maybe before summer 2020).