4 Replies Latest reply: May 15, 2014 9:53 AM by Grant Perkins RSS

    Challenge to Reformat

    Scott Johnson

      I have a list that I am pulling in that contains several variations for fund names that I want to change into a consistant format.  An example of the data is as follows:

                                 

      CO SERV AREA #10 ZN 6-SERV CHG

      COUNTY SERVICE AREA #6       

      COUNTY SERVICE AREA #10 ZONE 6

      COUNTY SERVICE AREA #12.3    

      CO SERVICE AREA #39.2 Z OF B2

      CSA #71.3 CITY-SEWER CHARGES

      CSA #95 - CONSTRUCTION

      CSA 71-CAPITAL IMPROVEMENT   

       

      As you can see, they were pretty sloppy in how they went about naming this group of funds.  The format that I would like to change them all to is:

       

      CSA 71.3 ZN 6

       

      This format would need to change based on the decimal that is sometimes present in the name and/or the zone number which is not always present.  When it is not present I would like to remove the "ZN" portion and just have "CSA 71.3".

      My thoughts so far are to somehow use the numbers in the various names and pull the numeric portions of the field and then recreate them in the format that I am looking for.  Any ideas on how I might go about doing this?  Any help is greatly appreciated.

       

      Thanks

        • Challenge to Reformat
          Scott Johnson

          Sorry forgot an important item.  I am using Monarch Pro Version 6.01.

           

          Thanks again

          • Challenge to Reformat
            Grant Perkins

            Scott,

             

            Just to confirm my understanding, is this the result you need for each of the lines you posted. The fifth line in particular?

             

            CSA 10 ZN 6

            CSA 6       

            CSA 10 ZN 6

            CSA 12.3    

            CSA 39.2 ZN B2

            CSA 71.3

            CSA 95

            CSA 71

             

             

            Quite an interesting challenge. Have you checked it will not combine different funds to the same name or would that not matter anyway?

             

            Are these records mixed up with others that do not start with a variation of the full CSA name? (In otherwords if we were to check whether a name started with CSA or not could  we simply assume that any that did not start "CSA" could be changed to CSA?. Anything to the left of a # for example ...

             

            Grant

            • Challenge to Reformat
              Scott Johnson

              First question, the format is correct, however on line 5 "CSA 39.2 ZN 2" would be the correct format, the "B" referred to Zone of Benefit.  I thought it might be easier to use just numbers and throw out the rest for simplicity.

               

              Question 2, all of the funds will have a unique numeric number that this description will attach to so if two of them end up having the same description that is OK.  This is considered a "short fund name", there is another data field that has the long name with no abbreviations, although not in a consistent format either. (another project for another day)

               

              Question 3, all of the funds start with some variation of CSA and if you noticed,(I am sure you did) one of the variations did not include a "#" before the CSA number.

               

              My first thoughts were that somehow I could key off of the numbers using some variation of IF, IsAlpha to define the location in the string of the numbers and then pull those character locations to create a new string. ("CSA "+"(number)", etc).  I know that I am going to run into difficulties with the variable zones.  One piece of information that might help also is that none of the CSAs have a number higher than 99 but there are several that have either one or two trailing decimals.

               

              I am able to go through the process in excel using find/replace several times to get to where I need to be, but I thought that there might be a way to clean it up through Monarch so that the next person that has to do this can simply use the models.

               

              Thanks again for the help.

              • Challenge to Reformat
                Grant Perkins

                Scott,

                 

                I think your first thoughts are very much on the right lines for a V6 approach.

                 

                There are some functions added in V7 that would make things a little easier and V8 has a REPLACE function that would make it much easier to normalize ZONE and "Z of B" to ZN!

                 

                However, I have a model developed with V6 for you to play with - there is a PM for you as well.

                 

                 

                Grant