12 Replies Latest reply: May 15, 2014 9:59 AM by Winn _ RSS

    Address split

    Peter _

      Having difficulty splitting addresses that are on one line.

      Example:

      ANNA JONES 100 FIELD COURT OAK RIDGE, NJ  07054

      ANNA SMITH 707 WHIPPANONG WAY RIDGEWOOD, NJ  07054

       

      I tried different traps with no results. Anybody have any ideas.

      Thanks

      Peter

        • Address split
          Grant Perkins

          Peter,

           

          Is there always a numeric between the name and the rest of the address? (Here in UK I would have to assume a NO to that question).

           

          Is the comma at the end of the address and before state code always there AND do any of the records also have commas elsewhere in the address details?

           

          Grant

           

          Originally posted by Peter:

          Having difficulty splitting addresses that are on one line.

          Example:

          ANNA JONES 100 FIELD COURT OAK RIDGE, NJ  07054

          ANNA SMITH 707 WHIPPANONG WAY RIDGEWOOD, NJ  07054

           

          I tried different traps with no results. Anybody have any ideas.

          Thanks

          Peter /b[/quote]

          • Address split
            Peter _

            Grant,

             

            Some addresses start off with a PO Box.  There is always a comma after the state. Also, there are some commas in the name field, ie john doe, md.

             

            Thanks

            Peter

            • Address split
              Grant Perkins

              OK.

               

              Sounds like it would be quite complex to come up with anything that would give consistent results reliably across an entire database, even if we simply looked for a way to split the name information at the beginning and the State and zip fields at the end from the body of the address.

               

              I don't claim to be any sort of expert on US address format re-formatting solutions having had small battles with similar problems before. Maybe one of the other members has already had some dealings with this sort of problem and would be able to provide an 'off the shelf' response.

               

              If not I will come back to in a few days (I will be away for 3 days) but would ideally like a longer comprehensive file sample to be able to check the effectiveness of any ideas I may have.

               

              Grant

               

              Originally posted by Peter:

              Grant,

               

              Some addresses start off with a PO Box.  There is always a comma after the state. Also, there are some commas in the name field, ie john doe, md.

               

              Thanks

              Peter /b[/quote]

              • Address split
                Mike Urbonas

                I know this is a bit of a "cop out" but may as well suggest the easiest solutions first...

                 

                Is it possible to revise the name and address list itself to insert a character after the name and street address?

                 

                For example, if you could get the list to display as:

                 

                ANNA JONES %100 FIELD COURT %OAK RIDGE, NJ 07054

                ANNA SMITH %707 WHIPPANONG WAY %RIDGEWOOD, NJ 07054

                 

                the Monarch job (trap each line as one single big field, then use calculated fields using LSPLIT) becomes a piece of cake.

                 

                Try looking into this first...

                • Address split
                  Grant Perkins

                  Darn it Mike, that was my plan B!

                   

                  But I suspect, from what Peter has said, that the list may be a bit more difficult to interpret. Maybe it means going back to the source (is that possible Peter?) and getting a different format of output supplied, selected for its ability to ease the task.

                   

                  If that is not possible we can simply attempt the challenge and see if any usable structural rules can be spotted. Gotta find some challenges somewhere.   

                   

                  Grant

                   

                  Originally posted by Mike Urbonas:

                  I know this is a bit of a "cop out" but may as well suggest the easiest solutions first...

                   

                  Is it possible to revise the name and address list itself to insert a character after the name and street address?

                   

                  For example, if you could get the list to display as:

                   

                  ANNA JONES %100 FIELD COURT %OAK RIDGE, NJ 07054

                  ANNA SMITH %707 WHIPPANONG WAY %RIDGEWOOD, NJ 07054

                   

                  the Monarch job (trap each line as one single big field, then use calculated fields using LSPLIT) becomes a piece of cake.

                   

                  Try looking into this first... /b[/quote]

                  • Address split
                    Peter _

                    Grant

                     

                    The source is from an excel spreadsheet. The whole record is in one cell.

                     

                    Peter

                    • Address split
                      Grant Perkins

                      Hi Peter,

                       

                       

                      This may be a silly question but ...

                      Any chance of going back to a stage somewhat before the Excel sheet?

                       

                      Grant

                       

                       

                      Originally posted by Peter:

                      Grant

                       

                      The source is from an excel spreadsheet. The whole record is in one cell.

                       

                      Peter /b[/quote]

                      • Address split
                        Peter _

                        Hi Grant,

                         

                        Sorry no, the spreadsheet was sent to us from a client.

                         

                        Peter

                        • Address split
                          Grant Perkins

                          Originally posted by Peter:

                          Hi Grant,

                           

                          Sorry no, the spreadsheet was sent to us from a client.

                           

                          Peter /b[/quote]I had a feeling you might say that!

                           

                          Still might be worth asking if they have access to the name and address data in a different form/format as long as you have some way of linking back to any other data on the excel sheet.

                           

                          The only other hope I can think of before life gets tricky would be that the data is actually formatted in fixed length chunks - but somehow I doubt that.

                           

                          Failing that you may be able to split most of the records (perhaps all but ...) by separating the name from the address by using the numeric OR "PO BOX" string of characters. A long shot but it may work. Forget the idea if you have only a few records to deal with - manual retyping might be quicker!!!!

                           

                          As for the rest, maybe split the State/Zip using the comma OR RSPLIT the line into 3 sections using the <space> and then take 1 part for the zip, another for the State and can the rest.

                           

                          Or ideas alonng those lines.

                           

                          The middle section of the address may need to be left as it is unless you can do something clever with an external lookup to a zip/address database tool.

                           

                          Just a few ideas off the top of my head. Happy to try to take them further with access to a file to play with.

                           

                          Grant

                          • Address split
                            Peter _

                            Grant,

                             

                            Through excel, I was able to cut here and there.  It was a little messy, but I got the job done.  Thanks for all your help.

                             

                            Peter

                            • Address split
                              Grant Perkins

                              Hi Peter,

                               

                              Glad you got the requirement sorted. I played around a little and came up with a way to split the Address part of the line from the address part based on finding the location of either the PO(box) or NUMBER string on the line. It can be adapted to cater for names with 2, 3, 4 or more components though it may get complicated! Nested IF statements usually do (get complicated) I find, but others may disagree with me on that.

                               

                              Of course some records may not obey the 'start of address' identification rules ...

                               

                              Here is the code based on your sample and a few variants I created.

                               

                              [font="courier"] (if(isalpha(LSplit(A,3," ",3))=0,(LSplit(A,3," ",3)),(if(left(LSplit(A,3," ",3),2)="PO",(LSplit(A,3," ",3)),(LSplit(A,4," ",4)))))) /font[/quote]There may be some tidying up that would make it more readable but it seems to work. I have not tested it very rigourously. I suspect there might be a simpler version or approach possible.

                               

                              Basically it assumes that the address can be identified as starting either with the string "PO" or a number. (In its current form it may not work correctly if the line started with a 3 part name and the third part started PO or for one or two other situations. However IF those situations existed in enough volume to be a problem worth coding around the idea could be refined to be more specific.)

                               

                              It would be even better if we could use "POBox" or a number, but the best route can only be assessed (and a decision taken about time to be spent on the project!)looking at a full example of the data.

                               

                              I have assumed there are at least 2 parts of data in the name section. I allowed for 3 parts. If there might be more the formula needs to be extended to cope with that.

                               

                              Once we have isolated the Address information we can work back to get the Name information. (If there are always 2 parts to the name data we don't need to do any of this complicated stuff - but I suspect that would not be the case).

                               

                              We can identify the start string for the address by using the LEFT function for a number of characters from the start of the new field. We can then go back and LSPLIT the line using the string from the LEFT function to break the line into 2 sections and use the first section for our name data. (Which in turn can be split using the LSplit function if required.)

                               

                              We can separate the State and ZIP from the rest of the address in a few ways. Lsplit using the "," would seem to work, or RSplit using Space to break the field into 3 sections.

                               

                              The only problem would be trying to split the body of the address if that was required. I doubt that it would be easy to define a consistent rule for that - but you never know ...

                               

                              Anyway, I thought you may find this idea interesting and maybe useful at some future point so I thought I would post it even though you have solved your immediate problem.

                               

                              Best regards,

                               

                              Grant

                               

                                Originally posted by Peter:

                              Grant,

                               

                              Through excel, I was able to cut here and there.  It was a little messy, but I got the job done.  Thanks for all your help.

                               

                              Peter /b[/quote]

                               

                              [size="1"][ August 15, 2003, 08:42 PM: Message edited by: Grant Perkins ][/size]

                              • Address split
                                Winn _

                                Sorry, that I have not responded sooner. I have dealt with a couple of situations similar to this one. There is no easy way to handle these types of reports. I have used a different method than the one Grant has proposed.

                                 

                                I use a floating trap to break up a line into separate fields at each space. I verify that my field widths are large enough. I then begin assembling the name and address information from the various parts. This is a very laborious process involving the creation of a number of intermediate fields. The advantage is that it is easy to update when you find exceptions to the rule. I don't have time to give an example at the moment, but I will post one soon.