A little addendum about data types to the +N(“comment”) in-cell formula comment trick in Excel

  1. You are relying with this well-documented trick on the fact that addition of 0 will altering a numeric value.
  2. However, what is not mentioned: not all work in Excel involves the number data type.
    1. Try adding your comment or zero to a string data type, and you will get a #VALUE error.
    2. Boolean, however, works also, since – as you can see when you have Excel evaluate your formula – if  you try adding 0 to a Boolean data type, Excel casts the prior Boolean FALSE to 0 or TRUE to 1, and casts the result of your addition (of 0 = nothing) back to the same initial Boolean for further evaluation. excel-evaluate-fromula-w-n-comment boolean
    3. So just put your +N()-comments in those part of your formula that involve either number or Boolean data type, and avoid adding to strings, for example:

    =IF(
        OR(
            ISBLANK(
                [Related issues]
            ),
            (            ISERR(
                    FIND(
                        "#",
                        [Related issues]
                    )
                ) )
        ) +
        N(
            "do nothing if no related task in col:related issues"
        ),
        "",
        IF(
            NOT(
                ISERR(
                    FIND(
                        ",",
                        [Related issues]
                    )
                )
            ) +
            N(
                "do nothing if no 2nd value in col:related issues"
            ),
            CONCATENATE(
                MID(
                    MID(
                        [Related issues],
                        FIND(
                            ",",
                            [Related issues]
                        ) + 2,
                        LEN(
                            [Related issues]
                        ) -
                        FIND(
                            ",",
                            [Related issues]
                        ) + 1 +
                        N(
                            "extracted the 2nd value from col:related issues"
                        )
                    ),
                    FIND(
                        "#",
                        MID(
                            [Related issues],
                            FIND(
                                ",",
                                [Related issues]
                            ) + 2,
                            LEN(
                                [Related issues]
                            ) -
                            FIND(
                                ",",
                                [Related issues]
                            ) + 1
                        )
                    ) + 1,
                    4
                ) +
                N(
                    "extracted the 4# of id of related issue"
                ),
                MID(
                    MID(
                        [Relatedissues],
                        FIND(
                            ",",
                            [Relatedissues]
                        ) + 2,
                        LEN(
                            [Relatedissues]
                        ) -
                        FIND(
                            ",",
                            [Relatedissues]
                        ) + 1
                    ),
                    FIND(
                        " ",
                        MID(
                            [Related issues],
                            FIND(
                                ",",
                                [Related issues]
                            ) + 2,
                            LEN(
                                [Related issues]
                            ) -
                            FIND(
                                ",",
                                [Related issues]
                            ) + 1
                        )
                    ) + 1,
                    2 +
                    N(
                        "appended to or from to the 4# of id of related issue"
                    )
                )
            ),
            ""
        )
    )

    1. This splits, extracts and copies values from a none to multi-value column for better sortability: image

Fun with .docx to .html transforms by means of HtmlConverter from PowerTools for Open XML

  1. The transform is FOSS and platform-independent:
    1. It neither requires Office nor Windows (The OpenXML SDK runs on Linux via Mono on the server.
    2. However, the most recent installment of Powertools for OpenXML, a high-level API to the OpenXML SDK, comes with a PowerShell interface (benefit: no Visual studio requirement).
  2. Valuable features of the transform,  among many other things, are:
    1. HtmlConverter is able to translate MS-Word styles into CSS (insofar needed – my code style has “No proofing” set, however, this cannot be implemented on the WWW), so the layout is preserved as designed, but w/o need for inline formatting:
        span.pt-StrongEmphasis-000052 {
            font-family: Calibri;
            font-size: 11pt;
            font-style: italic;
            font-weight: bold;
            margin: 0in;
            padding: 0in;
        }

        span.pt-lowCodeConsoleChar0 {
            color: #FFFFFF;
            background: #000000;
            font-family: Consolas;
            font-size: 10pt;
            font-weight: normal;
            margin: 0in;
            padding: 0in;
        }
     <h3 dir="ltr" class="pt-000040">
            <span class="pt-000041">2.2.1</span><span class="pt-000042"><span class="pt-000043">&nbsp;</span></span><span class="pt-Heading2Char"><b>References</b></span>
          </h3>

          <p dir="ltr" class="pt-BodyText">
            <span class="pt-DefaultParagraphFont-000003"><br />
            &lrm;</span><span class="pt-000000">&nbsp;</span>
          </p>

          <h1 dir="ltr" class="pt-000006">
            <span class="pt-000007"><b>3</b></span><span class="pt-000008"><b><span class="pt-000009">&nbsp;</span></b></span><span class="pt-Heading1Char"><b>Introduction</b></span>
          </h1>

          <h2 dir="ltr" class="pt-000018">
            <span class="pt-000019">3.1</span><span class="pt-000020"><span class="pt-000021">&nbsp;</span></span><span class="pt-Heading2Char"><b>Purpose of Document</b></span>
          </h2>
    1. There are many more options that I have not yet tried:
            SimplifyMarkupSettings simplifyMarkupSettings = new SimplifyMarkupSettings
            {
                RemoveComments = true,
                RemoveContentControls = true,
                RemoveEndAndFootNotes = true,
                RemoveFieldCodes = false,
                RemoveLastRenderedPageBreak = true,
                RemovePermissions = true,
                RemoveProof = true,
                RemoveRsidInfo = true,
                RemoveSmartTags = true,
                RemoveSoftHyphens = true,
                RemoveGoBackBookmark = true,
                ReplaceTabsWithSpaces = false,
            };
            MarkupSimplifier.SimplifyMarkup(wordDoc, simplifyMarkupSettings);

            FormattingAssemblerSettings formattingAssemblerSettings = new FormattingAssemblerSettings
            {
                RemoveStyleNamesFromParagraphAndRunProperties = false,
                ClearStyles = false,
                RestrictToSupportedLanguages = htmlConverterSettings.RestrictToSupportedLanguages,
                RestrictToSupportedNumberingFormats = htmlConverterSettings.RestrictToSupportedNumberingFormats,
                CreateHtmlConverterAnnotationAttributes = true,
                OrderElementsPerStandard = false,
                ListItemRetrieverSettings = new ListItemRetrieverSettings()
                {
                    ListItemTextImplementations = htmlConverterSettings.ListItemImplementations,
                },
            };
  1. One would really wish there was a way to get such HTML cleaned up automatically (ouch!):
  2.                <span class="pt-DefaultParagraphFont-000006">M</span>
                    <span class="pt-DefaultParagraphFont-000006">anaged requirements for system integration&nbsp;</span>
                    <span class="pt-DefaultParagraphFont-000006">of Center</span>
                    <span class="pt-DefaultParagraphFont-000006">&nbsp;</span>
                    <span class="pt-DefaultParagraphFont-000006">software&nbsp;</span>
                    <span class="pt-DefaultParagraphFont-000006">with&nbsp;</span>
                    <span class="pt-DefaultParagraphFont-000006">iLearning</span>
                    <span class="pt-DefaultParagraphFont-000006">&nbsp;and with content production and management (BPD). To mitigate lack of integration of $50k LMS software investment into departmental workflow</span>
                    <span class="pt-DefaultParagraphFont-000006">,</span>
                    <span class="pt-DefaultParagraphFont-000006">&nbsp;</span>
                    <span class="pt-DefaultParagraphFont-000006">developed&nbsp;</span>
                    <span class="pt-DefaultParagraphFont-000006">and documented&nbsp;</span>
                    <span class="pt-DefaultParagraphFont-000006">software to automate</span>
                    <span class="pt-DefaultParagraphFont-000006">&nbsp;creation of 4K+ user accounts p.a., 30K+ learning documents and 100K+ interactive content paths in LMS.</span>
    
  3. There are also much more serious conversion errors:
    1. MS-Word displays a plain text content control and a repeating section content control within a table, containing one Combobox and one plain text content control per row, perfectly: openxml-convert-docxtohtml-error-word
    2. Convert-DocxToHtml gobbles the content completely (and so does Google Docs Preview): openxml-convert-docxtohtml-error-html The underlying HTML has just a blank table under each heading:
          <div class="pt-000001">
              <p dir="ltr" class="pt-qiCVHeading1">
                <span class="pt-DefaultParagraphFont-000002">Profile</span>
              </p>
            </div>
            <div align="left">
              <table border="1" cellspacing="0" cellpadding="0" dir="ltr" class="pt-000003" />
            </div>
            <div class="pt-000001">
              <p dir="ltr" class="pt-qiCVHeading1">
                <span class="pt-DefaultParagraphFont-000002">Technologies</span>
              </p>
            </div>
            <div align="left">
              <table border="1" cellspacing="0" cellpadding="0" dir="ltr" class="pt-000003" />
            </div>
        
    3. MS-Word shows:imageYet need to look in to the underlying XML to see whether the .docx is to blame for that…
    4. But HtmlConverter output in IE or Firefox: imageThe underlying HTML reveals that the css does not get applied in the right place:
  4.  	<tr>
                    <td class="pt-000079">
                      <p dir="ltr" class="pt-BodyTextSmall">
                        <span class="pt-BodyTextSmallChar-000081">AD</span>
                      </p>
                    </td>
                    <td colspan="2" class="pt-000079">
                      <p dir="ltr" class="pt-BodyTextSmall">
                        <span class="pt-BodyTextSmallChar-000081">Active Driector, Microsfot&rsquo;s directory implementation.</span>
                      </p>
                    </td>
                  </tr>
    
                  <tr>
                    <td class="pt-000086">
                      <p dir="ltr" class="pt-BodyTextSmall">
                        <span class="pt-000085">&nbsp;</span>
                      </p>
                    </td>
                    <td colspan="2" class="pt-000086">
                      <p dir="ltr" class="pt-BodyTextSmall">
                        <span class="pt-000085">&nbsp;</span>
                      </p>
                    </td>
                  </tr>
    
  5. One could image MS-Word acting less strictly than OpenXML PowerTools:Convert-DocxToHtml, like a web-browser tolerates and displays bad HTML. However, not only would need to be justified how MS-Word can also serve as the originating HTML WYSIWYG editor. The OpenXML PowerTools:Get-OpenXmlValidationErrors for both of the above documents does not seem to find any OpenXML errors that could explain the bad conversion (other than dozens of Sch_UndeclaredAttribute errors (Version-related? Not sure how this could be) , there is only a Pkg_PartIsNotAllowed relating to a glossary).
  • Also yet to do:
    1. When (not always!) does my page title end up as empty?
      <title></title>
    2. Defaults to doctype xhtml, not html(5).
  • Done:
    1. Pretty-printing. The HtmlConverter output defaults to all content (not css ) on 1 line (e.g. in the example from which above code is taken, 90000chars long). For human readability, and also possibly git tracking, pretty-printing would be better. Can be enforced like so (is there a better way? cannot see a user-configurable option for the SaveOptions enumeration):
    2. openXml\OxPt\OxPtCmdlets\OxPtHelper.cs:var htmlString = html.ToString(SaveOptions.None); // trp: requesting pretty-printing, was:html.ToString(SaveOptions.DisableFormatting);
      
  • How to watch a task you did not create in Redmine

    1. “Watching” tasks, i.e. receiving notifications of task updates, is the default for tasks – if you created them.
    2. “Watching” can also be turned on per task. Go to “issues”, image, click on the first task.
    3. In the upper right of the task page, click “Watch”, image
    4. Then click “next” to cycle through all tasks. A bit tedious even for small projects – is there a way to default to “watching all”. It does not come with the roles (I tried): image

    Stop ":Zone.Identifier:$DATA" files from being created…

    1. … by running gpedit.msc as admin and setting "user configuration / administrative templates / windows components/ attachment manager / "Do not preserve zone information in file attachments" to "Enabled         No. "
      1. I observed these files in the drive my virtualbox win 8.shares with the linux host.
      2. The answer is out there , of course,  just not with all the search terms. A more thorough security discussion is also available.
      3. Just seems not applicable to a share what virtualbox makes appear as a network folder to Windows while it really is not, which causes also other misleading security warnings also : image

    Customized Quick Access Toolbar

    word-Customized-Quick-Access-Toolbar

    Easing access to some important tools for serious  writing – and configuration that can not be done in a template,but is permachine…

    Fun with Zotero inserting citations and bibliographies

    1. If you can install Zotero’s word processor add-ins (for LibreOffice Writer or MS-Word).:
      1. Here are the self-explanatory tool tips of the command buttons for the MS-Word add-in: zotero-ribbon-addins-command-buttons-tooltips
      2. Here is the add-in in action, inserting first one, than multiple citations, followed by generation of a bibliography:
      3. zotero-how-to-insert-1-or-many-citations&generate-bibliography

    2. If you cannot, you can still use the “create bibliography from items” of Zotero (which itself can be run under portable  Firefox from a USB stick – no install needed at all). Here is a brief example and insert those into your writing; zotero-create-bibliography-from-item2clipboard2word

    Fun with Zotero managing bibliographic references

    1. Curation: word-create-list-style-with-errors
    2. Deduplication:
      zotero-merge-duplicates
    Follow

    Get every new post delivered to your Inbox.

    Join 100 other followers