5 Replies Latest reply: May 15, 2014 10:14 AM by elginreigner _ RSS

    Good way to parse suite or apartment out of street address?

    MonUserCJ _

      Hey all,

       

      I have an issue and was wondering what would be an elegant way to handle it. I often have to parse out a suite or apartment into a separate field from a street address field.

       

      This would involve looking for certain strings (e.g. "SUITE", "STE", "APT", etc.) and selecting a substring based on which prefix string is found.

       

      Because I would ideally like to keep the string that comes before the actual apartment number, how the substring formula would be used would depend on the length of the prefix (i.e. SUITE is longer than STE, etc.). I have tried to use an address block to parse the data, with little luck.

       

      Is there an elegant way to have Monarch attempt to find a number of different strings and undertake actions based on them? Right now, the only way I can think to do this is using a long series of nested if statements. I would appreciate any advice anyone can provide. Thanks.

        • Good way to parse suite or apartment out of street address?
          KeyserSoze _

          You might be able to use a formula similar to the following to accomplish what you need:

           

          IF(InStr("APT ",UPPER(Addr))>0,

          LSplit(LSplit(UPPER(Addr),2,"APT ",2),2," ",1),

          IF(InStr("APARTMENT ",UPPER(Addr))>0,

          LSplit(LSplit(UPPER(Addr),2,"APARTMENT ",2),2," ",1),

          IF(InStr("STE ",UPPER(Addr))>0,

          LSplit(LSplit(UPPER(Addr),2,"STE ",2),2," ",1),

          IF(InStr("SUITE ",UPPER(Addr))>0,

          LSplit(LSplit(UPPER(Addr),2,"SUITE ",2),2," ",1),

          IF(InStr("UNIT ",UPPER(Addr))>0,

          LSplit(LSplit(UPPER(Addr),2,"UNIT ",2),2," ",1),

          "")))))[/CODE]

           

          It converts the address field to uppercase, just in case any lower case character sneak into the field, as InStr is case sensitive. To add other test strings is pretty straightforward.

           

          It would be nice to use something like StrIn(Addr,"APT","STE","UNIT") as a custom function but that would need some kind of looping or recursiveness, I believe, which Monarch does not support.

           

          Does anyone have another solution?

            • Good way to parse suite or apartment out of street address?
              Olly Bond

              Hello everyone,

               

              I see Keyser has spotted the invisible data - and included trailing spaces - so RAPTOR AVE won't be parsed as containing "APT ". But address data is dirty stuff - and if you have customers in "Apt Road, Ibhayi, Port Elizabeth, Eastern Cape, South Africa" (trust me, it's on Google Maps, and worth the detour just to see that it runs parallel to APS road and APU road...) then you might lose them. Less exotically, there's a Unit Street in Providence, Rhode Island and another in Los Angeles.

               

              You can amend the If() to exclude these cases - but tread carefully. I wouldn't like to have to build the expression that excluded STE meaning SUITE from STE meaning SAINTE. In the UK, I think you can buy, or subscribe to, a database from the Royal Mail with the complete address data by postcode, so it should be possible to link to this and score your addresses on matches by number, house name, street name, town etc. and then pass out the remainder for manual checking.

               

              But this is something that CRM systems and good master data management should have addressed (pardon the pun) long before the data ends up in a report for Monarch...

               

              Best wishes,

               

              Olly

                • Good way to parse suite or apartment out of street address?
                  Olly Bond

                  Hello CJ,

                   

                  Sorry, missed a bit... Have you tried rsplit(;2;", ";1) to get everything to the right of the last comma?

                   

                  HTH

                   

                  Olly

                    • Good way to parse suite or apartment out of street address?
                      Grant Perkins

                      Just to reinforce Olly's observation about Address data quality ....

                       

                      If you get about 80% accuracy you are dealing with a fantastically good data set - especially if the source is known to be via manual input rather than some sort of high quality well verified address checking database.

                       

                      My advice would be to get what you can using the Address Block functionallity and some calculated fields but make heavy use of the 'Error' field flag in the Address Block feature and then monitor the results to refine where possible. Once you hit about 90% you will have got about as far as you can take it on a 'worthwhile return for the effort' basis.

                       

                      The good thing is that once you have your methods working they can be used on future models quite easily.

                       

                      If you are dealing with address formats from multiple countries ... the task should keep you busy for some time.

                       

                       

                      Grant

                        • Good way to parse suite or apartment out of street address?
                          elginreigner _

                          Until recently we used a product from Experian, QAS (Quick Address Solution). It did a very good job at separating data. It was used to scrub the address after Monarch had processed the file. It has since been replaced by a return file from our letter vendor.

                           

                          As Grant stated, if you're getting 80% accuracy, you're doing good. Unless the address is in a postal block, the Address Block function does not always return the best results. The error flag will help, but it depends on the data.