3 Replies Latest reply: May 15, 2014 10:00 AM by win_ch _ RSS

    Calculated field: Search by contain and return destinated value

    win_ch _

      I looked everywhere and do not know whether this function is available.  I would like to do a calculated field.  It would look at Column A's data and if it contains the word "management", then in the calculated field it would return word "mgmt".  If it contains the word "storage", then in the calculated field it would return word "storage fee".  I have many more "if, then" statements for this calculated field.  How do I do this? I tried "management", it did not work.  It seems that Monarch dose not have functionality for search by "contain" in the calculated field.  I am using Monarch Pro 5.0 PC.

       

      Appreciate it!

        • Calculated field: Search by contain and return destinated value
          Data Kruncher

          Hi, and welcome to the forum.

           

          This could be a bit tricky, depending upon how many terms you're looking to replace.

           

          Basically, you need the InStr function. The InStr function determines if a specified string exists anywhere in another string.

           

          If the specified string does exist, the function returns the integer position within the string where the substring exists, but if the substring does not exist in the longer string, the function returns a zero value.

           

          It would look like this:

          If(InStr("mgmt",ColumnA)>0,"management","")[/code]

           

          For multiple search terms, you'd use a nested If, along the lines of:

          If(InStr("mgmt",ColumnA)>0,"management",if(Instr("storage",ColumnA)>0,"storage fee",""))[/code]

           

          Nested Ifs can get a bit messy when combined with other functions and thus require some attention to detail to get the correct results. An important limitation for you with v5 is that your calculated field expressions can only be about 1,024 characters. So if you need a few search term replacements, you could well run out of room.

           

          Another approach would be to use a lookup table calculated field. The drawback is that lookup tables require manual editing.

           

          HTH,

          Kruncher