Coloring Parts Of A Text Block

Version 1

    Current Situation:

    We are working on getting our Project Managers out of PowerPoint and into entering their data into a SharePoint form so we can create views in Tableau. They are accepting the change but there has been one pushback area.

     

    Problem:

    The way the SharePoint form is set up the PMs enter their status in a single text field. They add new stuff to the top (or are supposed to but sometimes they don't) and they want all new stuff entered in BLUE. Tableau doesn't allow taking part of a text field and formatting it. It's either all or nothing.

     

    Solution:

    I instructed them to put an asterisks in front of new items, all new items must be in the same paragraph, and don't use an asterisks anywhere else in the text. Once they did agreed to that, the rest was easy.

     

    Result Output:

    6-10-2019 11-23-29 AM.png

     

    How To Do:

     

    I broke out my first Row

     

    Row 1

    IF FIND([Comments], CHAR(10)) > 0 THEN
        LEFT([Comments], FIND([Comments], CHAR(10)))
    ELSE
        LEFT([Comments], LEN([Comments]))
    END

     

    I search for a hard carriage return and, if I find it, bring in everything from the beginning to that carriage return.

    If I don't find one, bring in everything from the beginning to the end

     

    Once I have Row 1, making Row 2 (and the other rows) becomes a matter of removing the previous row from the comments and repeating the process

     

    Row 2

    IF FIND(REPLACE([Comments],[Row 1],""), CHAR(10)) > 0 THEN
        LEFT(REPLACE([Comments],[Row 1],""), FIND(REPLACE([Comments],[Row 1],""), CHAR(10)))
    ELSE
        LEFT(REPLACE([Comments],[Row 1],""), LEN(REPLACE([Comments],[Row 1],"")))
    END

     

    Row 3

    IF FIND(REPLACE(REPLACE([Comments],[Row 1],""),[Row 2],""), CHAR(10)) > 0 THEN
        LEFT(REPLACE(REPLACE([Comments],[Row 1],""),[Row 2],""), FIND(REPLACE(REPLACE([Comments],[Row 1],""),[Row 2],""), CHAR(10)))
    ELSE
        LEFT(REPLACE(REPLACE([Comments],[Row 1],""),[Row 2],""), LEN(REPLACE(REPLACE([Comments],[Row 1],""),[Row 2],"")))
    END

     

    etc.

     

    Now that I have the rows broken out I glue them back together

     

    New Stuff

    IF ISNULL(IF FIND([Row 1],"*") > 0 THEN [Row 1] END) THEN "" ELSE IF FIND([Row 1],"*") > 0 THEN [Row 1] + CHAR(10) END END
    +
    IF ISNULL(IF FIND([Row 2],"*") > 0 THEN [Row 2] END) THEN "" ELSE IF FIND([Row 2],"*") > 0 THEN [Row 2] + CHAR(10) END END
    +
    IF ISNULL(IF FIND([Row 3],"*") > 0 THEN [Row 3] END) THEN "" ELSE IF FIND([Row 3],"*") > 0 THEN [Row 3] + CHAR(10) END END
    +
    IF ISNULL(IF FIND([Row 4],"*") > 0 THEN [Row 4] END) THEN "" ELSE IF FIND([Row 4],"*") > 0 THEN [Row 4] + CHAR(10) END END
    +
    IF ISNULL(IF FIND([Row 5],"*") > 0 THEN [Row 5] END) THEN "" ELSE IF FIND([Row 5],"*") > 0 THEN [Row 5] + CHAR(10) END END

     

    Old Stuff is the same but =0 instead of >0

     

    Now that you have two different fields for the comments formatting is now easy

     

     

     

    Workbook attached (Version 10.5)