+ Reply to Thread
Results 1 to 11 of 11

Thread: PARSE/TRIM AN ADDRESS TEXT FIELD

  1. #1
    Tyson Barlow
    Join Date
    May 2011
    Posts
    25
    Points
    0
    Answers Provided
    0


    0

    Default PARSE/TRIM AN ADDRESS TEXT FIELD

    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!

  2. #2
    Amen Abdou
    Join Date
    Jan 2011
    Posts
    7
    Points
    0
    Answers Provided
    0


    0

    Default Re: PARSE/TRIM AN ADDRESS TEXT FIELD

    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.
    Amen Abdou
    Calvin, Giordano & Associates

  3. #3
    Joe Borgione

    Join Date
    Apr 2010
    Posts
    1,140
    Points
    307
    Answers Provided
    31


    0

    Default Re: PARSE/TRIM AN ADDRESS TEXT FIELD

    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?
    Joe Borgione
    AlpineGeographic

    If the suggestion I provide helps solve your problem, please feel free to give it a favorable review by clicking the up arrow. That's only only reward I get for sharing 20 + years of professional GIS experience.

  4. #4
    Terry Silveus
    Join Date
    Aug 2010
    Posts
    352
    Points
    27
    Answers Provided
    5


    0

    Default Re: PARSE/TRIM AN ADDRESS TEXT FIELD

    Quote Originally Posted by amenabdou View Post
    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.

  5. #5
    Greg Keith
    Join Date
    Jun 2010
    Posts
    958
    Points
    243
    Answers Provided
    26


    0

    Default Re: PARSE/TRIM AN ADDRESS TEXT FIELD

    Quote Originally Posted by tlsilveus View Post
    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.

  6. #6
    Tyson Barlow
    Join Date
    May 2011
    Posts
    25
    Points
    0
    Answers Provided
    0


    0

    Default Re: PARSE/TRIM AN ADDRESS TEXT FIELD

    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

  7. #7
    Joe Borgione

    Join Date
    Apr 2010
    Posts
    1,140
    Points
    307
    Answers Provided
    31


    0

    Default Re: PARSE/TRIM AN ADDRESS TEXT FIELD

    Quote Originally Posted by tysonbarlow View Post
    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....
    Joe Borgione
    AlpineGeographic

    If the suggestion I provide helps solve your problem, please feel free to give it a favorable review by clicking the up arrow. That's only only reward I get for sharing 20 + years of professional GIS experience.

  8. #8
    Tyson Barlow
    Join Date
    May 2011
    Posts
    25
    Points
    0
    Answers Provided
    0


    0

    Default Re: PARSE/TRIM AN ADDRESS TEXT FIELD

    Quote Originally Posted by jborgion View Post
    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!

  9. #9
    Bruce Harold

    Join Date
    Oct 2009
    Posts
    414
    Points
    75
    Answers Provided
    10


    0

    Default Re: PARSE/TRIM AN ADDRESS TEXT FIELD

    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
    Bruce H.
    ESRI Redlands

  10. #10
    Greg Keith
    Join Date
    Jun 2010
    Posts
    958
    Points
    243
    Answers Provided
    26


    0

    Default Re: PARSE/TRIM AN ADDRESS TEXT FIELD

    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
    Code:
    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.

  11. #11
    Tyson Barlow
    Join Date
    May 2011
    Posts
    25
    Points
    0
    Answers Provided
    0


    0

    Default Re: PARSE/TRIM AN ADDRESS TEXT FIELD

    Quote Originally Posted by bruce_harold View Post
    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!

+ Reply to Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts