3 Replies Latest reply: May 15, 2014 10:11 AM by AlexM _ RSS

    Dealing with a multiple or a nested REPLACE

    AlexM _

      Linked in to my last posting, if I use the Proper() formula to correct the names of products currently in UPPER case, I'll need to replace a few results or oddities to a new set of preferences, i.e. "'S" to "'s", "And" to "and", "The" - where not the first word - to "the", etc.


      What's the best way to build up these multiple replaces? I can't seem to use multiple Replace() strings nor can I seem to wrap these into workable nested If() statements.


      Any help appreciated.



        • Dealing with a multiple or a nested REPLACE
          Grant Perkins

          Hi Alex,


          If this is a once off correction for each entry (rather than something that will need to be repeated each time the product names are presented for analysis) AND you really are talking about a "few" results, then I would be tempted to suggest producing an automated output for the new description field in so far as that is possiboe without to much effort but add a USER ENTERED field to the model to that operator correction can be applied.


          Add another calculated field for the final approved output. Set this so that is uses the automated result UNLESS there is an entry in the User Entered field, in which case the User field should be applied.


          Whoever is given the task of checking can then cut and paste the Auto field to the User field, make corrections and then allow the model to to update the final approved calculated field.


          Going back a stage ... you may be able to set a warning flag for dubious records post Automated adjustment IF you can work out the rules to do so. It would be a handy thing to do but if in place will be relied upon by the users. If so it needs to be 100% accurate at spotting potential anomalies that are indeed anomalies. It may not be entirely successful for a number of reasons and if it isn't one might conclude, as a user, that the attempt would be worse than useless.






            • Dealing with a multiple or a nested REPLACE
              Olly Bond

              Hello both,


              There is another option, to define a User Defined Function called "ProperStopList", based on an input field (e.g. ) and a user defined field called StopList. Edit StopList to just be a list of words with spaces between them which you want to be forced to Lower case. Then edit the function to take Title & StopList and do the hard slog of repeating or nesting the Replace function for all the instances in StopList. It's a bit of a crude cut & paste hack but it should give you results.


              Best wishes,



                • Dealing with a multiple or a nested REPLACE
                  AlexM _

                  Thanks for the suggestions.


                  Olly: Yours sounds like a good idea but I might need an example to understand this better. The placement of words is also a vital part of the rules so if, say, a product begins with the word "AT ", it should return "At " but, if elsewhere, it should return "at ".


                  Given that it's such a pain and there are so many variants involved, it's probably best to eyeball rather than tackle any other way...unless, of course, someone's already done the legwork and assembled such a complex user-defined field that can be shared. (One can only hope!)