Discussions

 View Only
  • 1.  Save to spreadsheet showing HTML characters in export

    Posted 01-02-2020 12:57
    When my team uses a notes field, and we export their work to Excel using the save as spreadsheet function, HTML characters appear in this field in the spreadsheet.  Is there a way for their notation to export without these characters?  Photos depict what the font looks like in QuickBase, and then when a spreadsheet is created.

    ------------------------------
    Teresa Maviglia
    ------------------------------


  • 2.  RE: Save to spreadsheet showing HTML characters in export

    Posted 01-02-2020 18:34
    You are just seeing what is actually in the field. That is a rich text field right? The browser interprets the HTML you have in there but Excel isn't able to. Is Notes - Customer a formula field by any chance? Or is the information entered into there and the formatting changed by whomever is entering it at the time?

    If it is a formula field then you could possibly just have a secondary formula calculate to the same information without all of the HTML in there and have that field on the report instead. That should show up in Excel better than what you have. Another option would be, is the rich text field necessary? I know it is nicer looking. If they were just entering this into a regular text box it should not have the formatting issues you are seeing even if they have multiple lines.

    Maybe someone else knows something else more helpful but those would be my suggestions.


  • 3.  RE: Save to spreadsheet showing HTML characters in export

    Posted 01-02-2020 19:44
    Edited by Mark Shnier (Your Quickbase Coach) 01-02-2020 19:44
    There is a way to do this by formula if you have some patience and ask your users to limit their enthusiasm for the of this field.  For example, asking them not to use any color.

    You can create a formula text field to successively strip out the html.

    Here is an example of text in a Rich Text field

    hello  goodbye bold  italics  underline

    Then the formula below removes pairs of html - generally they come in pairs to start say underline <u> and then to end it with </u>.

    var text Raw = [My Rich Text Field];

    var text RemoveStrikeout = SearchAndReplace(SearchAndReplace($Raw,"<s>",""),"</s>","");

    var text SubstituteSpace = SearchAndReplace($RemoveStrikeout,"&nbsp;"," ");

    var text RemoveStrong = SearchAndReplace(SearchAndReplace($SubstituteSpace,"<strong>",""),"</strong>","");

    var text RemoveEM = SearchAndReplace(SearchAndReplace($RemoveStrong,"<em>",""),"</em>","");

    var text RemoveUnderline = SearchAndReplace(SearchAndReplace($RemoveEM,"<u>",""),"</u>","");

    $RemoveUnderline


    So you would run a report looking for where this formula field still contains a "<" character and then enhance the formula with yet another SerachAndReplace.  But this would not work for color as there are too many colors to trap (pretty much infinite colors).

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------