10 Replies Latest reply on Jun 6, 2011 9:25 AM by tysonbarlow

    PARSE/TRIM AN ADDRESS TEXT FIELD

    tysonbarlow
      Hi,

      I have a field containing about 29,000 records called ADD_SHOR_2 that contains address values. 
      Example: 352 W 600 N

      I need to parse/trim each address at the first space so that the address becomes W 600 N. 

      I have looked around for a while now, but can't seem to find the most efficient way to calculate the 29,000 records so the house numbers are trimmed off.

      Can someone please help.  I am just working with ArcView 10.

      I know how to export the table to an excel-compatible file, and do "text to columns" in excel, then join the table back to the original shapefile using a field that contains unique identifiers, but I think there should be an easier way to do this.

      Thank you for your help!
        • Re: PARSE/TRIM AN ADDRESS TEXT FIELD
          amenabdou
          Hey Tyson,

          To fully answer your question I would need a little more information than that, for example, is every single record standardized to that same format?  Does every house number have exactly 3 numbers in it?

          The easiest way to do what you are looking for is to start an editing session, open up that table, right click on the column, and select Field Calculator...

          Then, in the field calculator dialog box, type:

          Left ( [NAME_OF_FIELD] , 3 )

          and hit Ok.

          What that is going to do is take the Left-most 3 characters in the text string and calculate them back into your field.

          If you want to keep the original data in tact, simply create a new field before you start editing and do those steps clicking on the new field rather than the original one.

          The easiest way you could go about doing all the records if there are not only 3 characters in the house number is to select all of the records with that amount of characters and be sure to check the box in the Field Calculate at the bottom that says "Calculate selected records only" and change the number at the end of the "Left" statement to the appropriate number.
          • Re: PARSE/TRIM AN ADDRESS TEXT FIELD
            jborgion
            Perhaps someone fluent in Python will chime in on this one, but here is another VBA approach:  use the split function to crack out the address string into individual pieces and then write the ones you want back together.  Something like this (thouroghly UNTESTED):

            dim list
            dim street
            dim street0  ''''arrays are 0 based so item 0 is your house number
            dim street1 '''' pre dir
            dimstreet2  '''' street name
            dimstreet3  '''' suf dir

            list = split([ADDRESS_FIELD]," ")
            street1 = list1
            street2 = list2
            street3 = list3
            street = street1&" "&street2&" "street3

            This assumes that you are only working with 4 address components; HouseNumber, PreDir, StreetName, SufDir.  You  may have to add a street4, street5 etc if you have addresses that look like 1234 S Birmingham Hill View Ln.

            BTW, your example is obviously a Utah or Southeren Idaho address; where are you working?
            • Re: PARSE/TRIM AN ADDRESS TEXT FIELD
              tlsilveus
              Hey Tyson,

              To fully answer your question I would need a little more information than that, for example, is every single record standardized to that same format?  Does every house number have exactly 3 numbers in it?

              The easiest way to do what you are looking for is to start an editing session, open up that table, right click on the column, and select Field Calculator...

              Then, in the field calculator dialog box, type:

              Left ( [NAME_OF_FIELD] , 3 )

              and hit Ok.

              What that is going to do is take the Left-most 3 characters in the text string and calculate them back into your field.

              If you want to keep the original data in tact, simply create a new field before you start editing and do those steps clicking on the new field rather than the original one.

              The easiest way you could go about doing all the records if there are not only 3 characters in the house number is to select all of the records with that amount of characters and be sure to check the box in the Field Calculate at the bottom that says "Calculate selected records only" and change the number at the end of the "Left" statement to the appropriate number.


              Make sure you save a copy of you data first because this will give you the opposite of what you want.
              • Re: PARSE/TRIM AN ADDRESS TEXT FIELD
                recurvata
                Make sure you save a copy of you data first because this will give you the opposite of what you want.


                Yes, you would use the Right() function, not Left(). I'd use 4 instead of 3, given your example, to also remove the blank space between number and street.
                The problem with this approach is that it assumes all street numbers are 3 characters long. Also, not sure if ArcGIS 10 supports the Right() and Left() functions. You could either use Split() as Joe suggested, or, if supported, Right() w/Instr() -> Right([ADDRESS], Instr([ADDRESS], " ") - 1). Or something like that, exact form may be off.
                • Re: PARSE/TRIM AN ADDRESS TEXT FIELD
                  tysonbarlow
                  First of all, thanks to all those interested in helping me.

                  Not all 29,000 records have a three digit house number.  House numbers range from 1 to 6 digits.

                  The left(), right() functions are supported in 10, and I have experience using them in the past.  I thought of using the right() function and tried for a while before creating this thread, but I have 29,000 addresses ranging anywhere from 9 to 36 characters. 
                  For example, one address could be: 1234 E River Bottoms Rd.   

                  I am not at work now, but I will have to try using the right() and InStr() functions. 

                  Thank you

                  I am working in Utah.  Haha
                  • Re: PARSE/TRIM AN ADDRESS TEXT FIELD
                    jborgion
                    First of all, thanks to all those interested in helping me.

                    Not all 29,000 records have a three digit house number.  House numbers range from 1 to 6 digits.

                    The left(), right() functions are supported in 10, and I have experience using them in the past.  I thought of using the right() function and tried for a while before creating this thread, but I have 29,000 addresses ranging anywhere from 9 to 36 characters. 
                    For example, one address could be: 1234 E River Bottoms Rd.   

                    I am not at work now, but I will have to try using the right() and InStr() functions. 

                    Thank you

                    I am working in Utah.  Haha


                    There is also the Ubound() that you can use to figure out how may elements are in an array; being an old bourne shell/c shell/sed/awk/aml/perl/c  kind of guy, arrays are  part of my genetic make up, as a Utah addresses....
                    • Re: PARSE/TRIM AN ADDRESS TEXT FIELD
                      tysonbarlow
                      Perhaps someone fluent in Python will chime in on this one, but here is another VBA approach:  use the split function to crack out the address string into individual pieces and then write the ones you want back together.  Something like this (thouroghly UNTESTED):

                      dim list
                      dim street
                      dim street0  ''''arrays are 0 based so item 0 is your house number
                      dim street1 '''' pre dir
                      dimstreet2  '''' street name
                      dimstreet3  '''' suf dir

                      list = split([ADDRESS_FIELD]," ")
                      street1 = list1
                      street2 = list2
                      street3 = list3
                      street = street1&" "&street2&" "street3

                      This assumes that you are only working with 4 address components; HouseNumber, PreDir, StreetName, SufDir.  You  may have to add a street4, street5 etc if you have addresses that look like 1234 S Birmingham Hill View Ln.

                      BTW, your example is obviously a Utah or Southeren Idaho address; where are you working?


                      So I have tried this a number of different ways, but can't seem to get it right.  I can look at this and understand what is being done, but can't figure out the syntax in the Field Calculator. I have handled small, simple field calculations using functions, but not something a little more robust like this.  Any help on what the syntax should look like would be great.  I'm also not too familiar with the Pre-Logic Script Code window.  If this needs to be entered in that window, i'm not sure how to structure the code.  Thanks!
                      • Re: PARSE/TRIM AN ADDRESS TEXT FIELD
                        bharold-esristaff
                        Hi

                        Instead of grappling with string methods, try the Standardize Addresses geoprocessing function.
                        It will separate all the base address components, wnd you can then concatenate the non-house-number ones back into the data you want.

                        Regards
                        • Re: PARSE/TRIM AN ADDRESS TEXT FIELD
                          recurvata
                          I'd never heard of that Bruce, looks pretty handy, thanks.
                          But if you need a similar string function for something else, in the Field Calculator try
                          Right([SOME_FIELD], Instr([SOME_FIELD], " ") + 1)
                          

                          A space between the double quotes since that's the character you're looking for - a blank space - and add 1 to get the starting position of the next character. Also, I never remember if it's single or double quotes.
                          • Re: PARSE/TRIM AN ADDRESS TEXT FIELD
                            tysonbarlow
                            Hi

                            Instead of grappling with string methods, try the Standardize Addresses geoprocessing function.
                            It will separate all the base address components, wnd you can then concatenate the non-house-number ones back into the data you want.

                            Regards


                            Thanks Bruce!  This took 5 minutes, and I have exactly what I needed.  Now i'm kicking myself for not finding this earlier.  Thanks again!