Forum Discussion

JimHarrison's avatar
JimHarrison
Qrew Champion
3 years ago

Re: Finding non- windows-1252 Characters in Text fields using a formula

Nice! I have my ASCII windows-1252 reference (https://www.ascii-code.com/CP1251) on my desk, if I run across anything else I'll add it to the end of the list.

------------------------------
Jim Harrison
transparency = knowledge + understanding : The Scrum Dudes
------------------------------

3 Replies

Sort By
  • JimHarrison's avatar
    JimHarrison
    Qrew Champion
    3 years ago

    Werps, I guess the button that says reply in the email doesn't update the post. 
    We needed to see the special characters in a field so we can diagnose what is causing the problem. So I took what Mark posted and modified it so if it finds a special character it gets added to the final text of the field. 

    UPDATED!

    var text RawOne= URLEncode( [Scope Change Description]);

    var text HorizontalTab= ToText(If(Contains($RawOne, "%09"),"htab." ,"."));
    var text LineFeed= ToText(If(Contains($RawOne, "%0A"),"lf." ,"."));
    var text CarriageReturn= ToText(If(Contains($RawOne, "%0D"),"cr." ,"."));
    var text Space= ToText(If(Contains($RawOne, "%20"),"sp." ,"."));
    var text Exclamation= ToText(If(Contains($RawOne, "%21"),"exclm." ,"."));
    var text Quote= ToText(If(Contains($RawOne, "%22"),"quote." ,"."));
    var text Hash= ToText(If(Contains($RawOne, "%23"),"hash." ,"."));
    var text Dollar= ToText(If(Contains($RawOne, "%24"),"dollr." ,"."));
    var text Percent= ToText(If(Contains($RawOne, "%25"),"pcent." ,"."));
    var text Amphersand= ToText(If(Contains($RawOne, "%26"),"amp." ,"."));
    var text SingleQuote= ToText(If(Contains($RawOne, "%27"),"snglqt." ,"."));
    var text LeftRoundBracket= ToText(If(Contains($RawOne, "%28"),"lbrkt." ,"."));
    var text RightRoundBracket= ToText(If(Contains($RawOne, "%29"),"rbrkt." ,"."));
    var text Star= ToText(If(Contains($RawOne, "%2A"),"star." ,"."));
    var text Plus= ToText(If(Contains($RawOne, "%2B"),"plus." ,"."));
    var text Comma= ToText(If(Contains($RawOne, "%2C"),"comma." ,"."));
    var text Slash= ToText(If(Contains($RawOne, "%2F"),"slash." ,"."));
    var text Colon= ToText(If(Contains($RawOne, "%3A"),"colon." ,"."));
    var text SemiColon= ToText(If(Contains($RawOne, "%3B"),"secolon." ,"."));
    var text LessThan= ToText(If(Contains($RawOne, "%3C"),"lessthan." ,"."));
    var text Equals= ToText(If(Contains($RawOne, "%3D"),"equals." ,"."));
    var text GreaterThan= ToText(If(Contains($RawOne, "%3E"),"grtrthan." ,"."));
    var text Question= ToText(If(Contains($RawOne, "%3F"),"questn." ,"."));
    var text At= ToText(If(Contains($RawOne, "%40"),"At." ,"."));
    var text LeftSquareBracket= ToText(If(Contains($RawOne, "%5B"),"lsqbrkt." ,"."));
    var text BackSlash= ToText(If(Contains($RawOne, "%5C"),"bkslsh." ,"."));
    var text RightSquareBracket= ToText(If(Contains($RawOne, "%5D"),"rsqbrkt." ,"."));
    var text Caret= ToText(If(Contains($RawOne, "%5E"),"caret." ,"."));
    var text BackTick= ToText(If(Contains($RawOne, "%60"),"bktic." ,"."));
    var text LSingleQuote= ToText(If(Contains($RawOne, "%91"),"lsnglQuote." ,"."));
    var text RSingleQuote= ToText(If(Contains($RawOne, "%92"),"rsnglQuote." ,"."));
    var text LQuote= ToText(If(Contains($RawOne, "%93"),"lQuote." ,"."));
    var text RQuote= ToText(If(Contains($RawOne, "%94"),"rQuote." ,"."));
    var text NDash= ToText(If(Contains($RawOne, "%96"),"ndash." ,"."));
    var text MDash= ToText(If(Contains($RawOne, "%97"),"mdash." ,"."));
    var text MiddleDot= ToText(If(Contains($RawOne, "%B7"),"dot." ,"."));
    var text FractionOnHalf= ToText(If(Contains($RawOne, "%BD"),"half." ,"."));

    $HorizontalTab & $LineFeed & $CarriageReturn & $Space & $Exclamation & $Quote & $Hash & $Dollar & $Percent & $Amphersand & $SingleQuote & $LeftRoundBracket & $RightRoundBracket & $Star & $Plus & $Comma & $Slash & $Colon & $SemiColon & $LessThan & $Equals & $GreaterThan & $Question & $At & $LeftSquareBracket & $BackSlash & $RightSquareBracket & $Caret & $BackTick & $LSingleQuote & $RSingleQuote & $LQuote & $RQuote & $NDash & $MDash & $MiddleDot & $FractionOnHalf



    ------------------------------
    Jim Harrison
    transparency = knowledge + understanding : The Scrum Dudes
    ------------------------------
      • JimHarrison's avatar
        JimHarrison
        Qrew Champion
        3 years ago
        I added more values 

        var text RawOne= URLEncode([Scope Change Description]);

        var text ReplaceHorizontalTab= SearchAndReplace($RawOne, "%09", " ");
        var text ReplaceLineFeed= SearchAndReplace($ReplaceHorizontalTab, "%0A", "<br>");
        var text ReplaceCarriageReturn= SearchAndReplace($ReplaceLineFeed, "%0D", "<br>");
        var text ReplaceSpace= SearchAndReplace($ReplaceCarriageReturn, "%20", " ");
        var text ReplaceExclamation= SearchAndReplace($ReplaceSpace, "%21", "!");
        var text ReplaceQuote= SearchAndReplace($ReplaceExclamation, "%22", "\"");
        var text ReplaceHash= SearchAndReplace($ReplaceQuote, "%23", "#");
        var text ReplaceDollar= SearchAndReplace($ReplaceHash, "%24", "$");
        var text ReplacePercent= SearchAndReplace($ReplaceDollar,"%25", "%");
        var text ReplaceAmphersand= SearchAndReplace($ReplacePercent,"%26","&");
        var text ReplaceSingleQuote= SearchAndReplace($ReplaceAmphersand,"%27", "'");
        var text ReplaceLeftRoundBracket= SearchAndReplace($ReplaceSingleQuote,"%28", "(");
        var text ReplaceRightRoundBracket= SearchAndReplace($ReplaceLeftRoundBracket,"%29", ")");
        var text ReplaceStar= SearchAndReplace($ReplaceRightRoundBracket,"%2A","*");
        var text ReplacePlus= SearchAndReplace($ReplaceStar,"%2B", "+");
        var text ReplaceComma= SearchAndReplace($ReplacePlus, "%2C", ",");
        var text ReplaceSlash= SearchAndReplace($ReplaceComma,"%2F", "/");
        var text ReplaceColon= SearchAndReplace($ReplaceSlash,"%3A",":");
        var text ReplaceSemiColon= SearchAndReplace($ReplaceColon,"%3B", ";");
        var text ReplaceLT= SearchAndReplace($ReplaceSemiColon,"%3C","<");
        var text ReplaceEquals= SearchAndReplace($ReplaceLT,"%3D", "=");
        var text ReplaceGT= SearchAndReplace($ReplaceEquals,"%3E",">");
        var text ReplaceQuestion= SearchAndReplace($ReplaceGT,"%3F","?");
        var text ReplaceAT= SearchAndReplace($ReplaceQuestion,"%40","@");
        var text ReplaceLeftSquareBracket= SearchAndReplace($ReplaceAT,"%5B", "[");
        var text ReplaceBackSlash= SearchAndReplace($ReplaceLeftSquareBracket,"%5C","\\");
        var text ReplaceRightSquareBracket= SearchAndReplace($ReplaceBackSlash,"%5D", "]");
        var text ReplaceCaret= SearchAndReplace($ReplaceRightSquareBracket,"%5E", "^");
        var text ReplaceBackTick= SearchAndReplace($ReplaceCaret,"%60", "`");
        var text ReplaceLSingleQuote= SearchAndReplace($ReplaceBackTick,"%91", "'");
        var text ReplaceRSingleQuote= SearchAndReplace($ReplaceLSingleQuote,"%92", "'");
        var text ReplaceLQuote= SearchAndReplace($ReplaceRSingleQuote,"%93", "\"");
        var text ReplaceRQuote= SearchAndReplace($ReplaceLQuote,"%94", "\"");
        var text ReplaceNDash= SearchAndReplace($ReplaceRQuote,"%96", "-");
        var text ReplaceMDash= SearchAndReplace($ReplaceNDash,"%97", "-");
        var text ReplaceMiddleDot= SearchAndReplace($ReplaceMDash,"%B7", "-");
        var text ReplaceFractionOnHalf= SearchAndReplace($ReplaceMiddleDot,"%BD", "1/2");

        $ReplaceFractionOnHalf

        ------------------------------
        Jim Harrison
        transparency = knowledge + understanding : The Scrum Dudes
        ------------------------------