6 Replies Latest reply on Jun 20, 2013 7:36 AM by Amandine Ricaud

    Formula to identify spaces only in text field

    Amandine Ricaud

      Hi all,

       

      I would like to ask for your support in regards to a formula.

      I have a field in my MySQL database which contains comments. The issue is that sometimes this field contains text, sometimes nothing and sometimes spaces only.

       

      I would like to be able to differentiate when the field is empty because no comment was required from when the field looks empty but contains spaces only.

       

      I have been trying to use the below formula:

      IF([Comments]= " ") THEN "Invalid Comment: 1 space"

      ELSEIF([Comments]= "  ") THEN "Invalid Comment: 2 spaces"

      ELSEIF([Comments]= "   ") THEN "Invalid Comment: 3 spaces"

      ELSEIF([Comments]= "    ") THEN "Invalid Comment: 4 spaces"

      ELSEIF([Comments]= "     ") THEN "Invalid Comment: 5 spaces"

      ELSEIF([Comments]= "      ") THEN "Invalid Comment: 6 spaces"

      ELSEIF([Comments]= "") THEN "No comment required"

      ELSE(STR([Comments]) + " (ID: " + STR([uid])+")")

      END

       

      The formula works fine up to the point where I refresh the extract. Then all the occurences are "grouped" into the first case, here "Invalid Comment: 1 space".

      Therefore I was wondering whether there would be a solution to "fix" this formula or a better one to get the results I am looking for?

      Thank you in advance,

      Amandine