2 Replies Latest reply: Sep 27, 2017 2:30 PM by mclark _ RSS

    In Data Prep - Customizing Replace By Position

    mclark _



      Enjoying the new features in Monarch V14. Replace by position feature is great. I wondered if anyone knew of a way to combine replacing a value in a certain position IF it equals a certain value?


      So for example, replace position 5 if it equals 7:


      24357  ===REPLACE THIS ONE

      43167 ====REPLACE THIS ONE




      All suggestions welcome.


        • Re: In Data Prep - Customizing Replace By Position
          Stephen Smay



          The only way I can think to do this is with a calculated field.


          There is a function called Substr() that will check position 5 (or whatever position you need) in the string, and another function called Stuff() which allows you to replace only what's in a specified position. The total formula would look like this:


          If( Substr([Employee ID],5,1) = "7", Stuff([Employee ID],5,1,"Replacement String"), [Employee ID] )


          Keep in mind, these functions only work on strings, so if that field is numeric, you will need to convert it to text prior to entering this formula.


          If you need more explanation, let me know!