2 Replies Latest reply: May 15, 2014 10:02 AM by MuXi115 _ RSS

    Newb with Questions

    MuXi115 _

      Greetings,

       

      I have different fields in the same line that vary in their beginning and ending points.

       

      I need to be able to extract the name, street address, city/state, and zip code.

       

      Here is a sample:

       

      DOC NO   NPI   SPECIALTY   NAME AND ADDRESS                                                       

      CURR-PERIOD   

        UPIN          CURR. INP.                                                                               

      5512 1780678573   RAD     ABERNATHY JOHN M$PO BOX 2787$COLUMBUS GA 31994                       GA016500        GA016500      7       

      B62233              0      *    0      0      0  *     0      0      0 *      0      0      0 *         .00        .00          .00    

                     2085R0202X                                                                               

      1875 1538153903   NES     ADAMS WILLIAM E$1900 10TH AVE$COLUMBUS GA 31901                      GA018132        GA018132      7       

      D44671              0      *    8      0     54  *    24      0    180 *     83      0    606 *   720964.64        .00   4901203.22    

                     207T00000X                                                                               

      701870 1548215163   FAM     ADEGBILE JOY J$7196 NORTHLAKE DR$COLUMBUS GA 31909                   GA048967        GA048967      7       

      G21270              0      *    2      0     27  *     6      0    122 *      9      0    143 *    36249.05        .00    579759.30    

      581719867      207QG0300X                                                                               

      9399 1780642173   FAM     AGARWAL DEEPALI S$1030 CENTER STREET$COLUMBUS GA 31901               GA054783        GA054783      7       

      I10191              3      *    9      0     40  *    53      0    288 *     27      0    172 *   230583.75        .00   1058955.74    

                     207Q00000X                                                                               

      8961 1891753190   NEPH    AGARWAL RAVINDRA$700 CENTER ST$COLUMBUS GA 31901                     GA048134        GA048134      7       

      G41325              0      *    0      0      4  *     0      0     11 *      2      0     23 *      345.50        .00     76510.55    

                     207RN0300X                                                                               

      701755              CAR     AHMED MOHAMMAD$2055 E SOUTH BLVD$MONTGOMERY AL 36118                 GA019053        GA019053      7       

      F33630              0      *    0      0      0  *     0      0      0 *      1      0      2 *     1975.50        .00      5140.10    

      630888599      203BC0100X                                                                               

      /CODE

       

      Thanks for any suggestions!

       

      Josh

        • Newb with Questions
          Data Kruncher

          Hi Josh, and welcome to the forum.

           

          Get out your Ginsu knife as it's slice and dice time, as we're going to initially extract a large field and then logically break it up into the proper bits.

           

          To tackle this one, paint one large field beginning with the last name, and going all the way over to the end of the longest sample, in this case it's about position 83 on the line. Name this field "A".

           

          Now we can use Monarch's functions and Address block feature to get what you need, so switch over to the Table window.

           

          Create a calculated Name field with this expression:

          Left(A,Instr("$",A)-1)[/code]Good. Now the PreAddress field needs:

          LSplit(A,2,"$",2)[/code]And the Address field will use:

          Replace(PreAddress,"$"," ")[/code]Build a Street field using:

          LSplit(PreAddress,2,"$",1)[/code]OK. Now fire up the Address Blocks feature. Give it a name, say Address, and check only the first USA option. Click Next.

           

          Select only the Address field, and click Next.

           

          Select only the Region and Postal Code options. Unfortunately the others just don't work for your sample.

           

          Finally, create a City field, as follows:

          Trim(Left(RSplit(PreAddress,2,"$",1),Instr(Region+" ",RSplit(PreAddress,2,"$",1))-1))[/code]I would encourage you to spend some time reviewing the online help if any of these functions are new to you.

           

          As is often the case with Monarch, any time you bring calculations into play, there may well be other approaches or calculations which work just as well as others for the current requirement. The solutions presented above seem to work just fine.

           

          Finally, hide the PreAddress and Address fields as you won't need to see them now.

           

          HTH,

          Kruncher

            • Newb with Questions
              MuXi115 _

              Thank You for the help. Just about everything worked for me.

               

              I'm having trouble getting the Address Block to work properly. It doesn't seem to pick up the AddressRegion, AddressPostalCode, or City information. The column headers show, but there is not data in the columns.

               

              Thanks Again,

               

              Josh