1 Reply Latest reply: May 15, 2014 9:57 AM by Grant Perkins RSS

    Find a character string

    MC Smith

      In a calculated field, we want to be able to evaluate a field to see if it contains a character string.  If it does, we want to do a certain action and if it doesn't then another action.

      Ex. I want to find the word "from" within a character field.

        • Find a character string
          Grant Perkins



          I think you are looking at the IF() function together with the INSTR() function.


          INSTR(" from ", )


          would return a number - the position of the "f" in the first occurrence of the word "from" if it exists.


          So, in and IF() formula you would use something like;


          IF(INSTR(" from ", )>0, "then do this","Do something else")


          Note the spaces around 'from' if you want the word to appear separate from any other words containing that string.


          This means that if the field does contain the string " from " , the field would be filled with the text "then do this" and if it does not the field would be filled with "Do something else".


          If "then do this" is another formula just substitute that formula for the text. The same with "Do something else".


          Depending on what you want to do the formula could get quite complex. I may be best to create each part of it separately if possible and then combine everything when you know the constituent parts work.


          I hope this helps and is relevant to you requirement.