Field Calculator: I just want the third word

Usually once a year I have a need to calc a field to a word position in another field in ArcMap. For example, here is a field from some census data I downloaded:

fieldcalc1

Why did they add the text “Census Tract” to all of these? Seems to be a space waster right? All I really want is just the tract number. So what I will do is create a new field, then calc the value to the 3rd word, or tract number, from the other field.

First I create a new field named appropriately TRACTNUM. You simply do this using the attribute table window pulldown > Add field…

fieldcalc2

Next, I enter the field name, set the type to Text, and set the length to 10.

fieldcalc3

My tract numbers will not be longer than 10 characters. Press the OK button and the field will be added to the end of the attribute table.

Next, I scroll over to the new field name in the attribute table and right click on it to select the field calculator.

fieldcalc4

The field calculator appears. I am using ArcMap 10.1, so I select the Python parser, then double click on the NAMELSAD10 field that contains the tract numbers I want to extract, then set the type to String and click on the .split() function. Next in the expression area between () I enter a double quote, a space, and another double quote, then add [2] at the very end. The expression should look like the following:

fieldcalc5

What this will do is take the text from the NAMELSAD10 field, pass it to the split function which uses the space ” ” as the delimiter to separate the words and then extracts the third word for the TRACTNUM field calculation. In Python, position 0 would be the first word, position 1 the second word, and position 2 the third word, which we want. That is why I used [2].

With everything set in the field calculator, click the OK button. Once the calculation is complete, you will see just the tract numbers in the TRACTNUM field.

fieldcalc6

Now that I documented this, I will not forget how to do this next year! -mike

55 thoughts on “Field Calculator: I just want the third word

    • All very helpful but is there a way to extract a string up to a certain character, in this case a hyhpen(-) and just return everything prior to the hyphen? For example in the following string:
      “Mud Lake-Frame Relay” return back “Mud Lake”

      Thanks!

      • Michael,
        Many thanks for your response! I thought I had tried that but to no avail. I’ll keep this for future reference.
        Jim

  1. Pingback: Using Python with the Field Calculator in ArcGIS | GISC

  2. AHHH!!! Thank you! This helped me parse out an address field to its component parts. I’ll make sure I keep a link to this page and save the script. Yours was the only resource, online… in the WORLD, I could find on this subject that was exactly what I needed. Thanks again,
    Brian

  3. A helpful post indeed πŸ™‚

    However, I hit a slag.
    I’m dealing with parcels which are labelled like 000/0, 000 being the number of a parcel and 0 being the subnumber of a parcel if it has one. Because of the backslash, the field type is text. Basically, I can’t do any calculation with such fields. I can’t even sort the attributes ascending or descending because text needs a leading zero(s) to sort the numbers correctly. Therefore, I want to separate the parcel numbers and subnumbers. I separated the numbers with no problem using the split function you wrote about. However, it does not split the subnumbers. I guess that’s because most parcels don’t have subnumbers and are just labelled 000 without /0, so when I tell the function to use “/” as the delimiter to separate the numbers, Python gets confused since most parcels don’t have “/”, let alone a subnumber. Anyway, I was wondering if there’s a solution to this problem.
    Thank you in advance!

    • Hi Marko. One thought is that you select the records that have the “/” in them, then run the field calculator on them to get at the subnumbers in the selected set of records. If parcels with subnumbers always have 3 numbers before the slash, you can select by attributes using “FIELD_NAME” LIKE ‘___/%’ , where FIELD_NAME is your field that has the parcel number in it, three underscores represent any three characters, then the slash, then a percent which is just a wildcard for any number of characters after the slash. Give that a try.

      • Thank you!

        Indeed selecting the records that have a backslash is the most logical thing. The field calculator then makes no issues calculating only the selected features πŸ™‚

  4. Very helpful post! I’ve actually referred to it a couple of times to refresh my memory on how to do this. However, I am currently trying to parse a string that does not have a separator. Is there a similar function that will only return the first couple characters in a string?
    For example I have a field that has pipe diameter and pipe material in one string: “10AC” or “12DI”. I want to separate out the diameter into its own field. Any suggestions?

    • Hi Madeline. Great question. In your examples the pipe diameter are the first two characters, so your expression would contain
      !FIELD_NAME![:2], where [:2] extracts out the first two characters in FIELD_NAME. For example, if a record had FIELD_NAME = “10AC”, your new field would be calced to “10”. -mike

      • Thank you for the quick reply!! Especially on an old post πŸ™‚
        That worked perfectly and was way simpler than any of the equations I was trying.
        Cheers!

  5. I am attempting to grab text from a string field but what I am attempting to grab is within Parenthesis..

    For example, 123 South Metro A Line (0123)

    I am attempting to only grab the numbers within the parenthesis. Any ideas?

  6. Hi Mike –

    Do you know if this approach be used with labels? I am trying to return just the house number to label parcels with this:

    !PRC_PRIMARY_SITEADDR!.split(” “)[0]

    The code worked when testing in Field Calculator but not when used as an expression with labeling. I am looking for a more permanent solution and to avoid bringing all the data local in order to add the house number field.

    Thanks for any thoughts!

    klf

    • Hi Kayla. With an expression for labels, you will need to use brackets [ ] for your field names (no consistency right? Thanks ESRI!). You also might have some addresses that are blank which would cause a problem with your one line expression. You will need to click on the Advanced checkbox for the label expression and create a python function to pull out the address number. It will test to see if the address is not blank (length > 0), and if so, it will pull out the address number and set the variable to it, if it is blank, it will set the variable to blank. Here is the code to use:

      def FindLabel ( [PRC_PRIMARY_SITEADDR] ):
        addr = str([PRC_PRIMARY_SITEADDR])
        if len(addr.split()) > 0:
          addnum = addr.split(" ")[0]
        else:
          addnum = ""
        return addnum

      Make sure to indent the code like above, it is important to python. I hope that helps!

      mike

      • After thinking about this a little bit more, I thought I could do this with a one line expression instead of a function, but I forgot since you have to use brackets for field names, when you try to use [0] to pull out the first “word” or address number from the field, you get an error because ArcGIS thinks [0] is a field name! So you have to use a python function. However, you can do this with one line in the function and not worry about blank addresses. Using split(” “) will take care of any blanks and NULLs and just give them a space, which will label just fine. So you can use this expression instead:

        def FindLabel ( [PRC_PRIMARY_SITEADDR] ):
          return [PRC_PRIMARY_SITEADDR].split(" ")[0]

        For some reason the [0] is not interpreted as a field name within the function … go figure!

        mike

  7. Mike, that is absolutely awesome – works like a charm! Thank you also for explaining the “niggly” bits, and that the inconsistencies are not my imagination. You be the coding wizard! (o:

    klf

  8. I am trying to remove all the hyphens and periods from a parcel number (01-01-01-01-01-001.111) in a concatenation expression. I just want 0101010101001111 brought over. Is there away I can do that in an expression in the field calculator? Thank you in advance.
    Bart

    • Hi Bart. This should do it:
      !FIELD_NAME!.replace(“-“, “”).replace(“.”, “”)
      The value in field FIELD_NAME is passed to the first replace which will replace any “-” with a blank, and then is passed on to the second replace that will replace any “.” with a blank.
      -mike

      • Thank you Mike, but I am receiving an ERROR 000539 SyntaxERROR: invalid syntax (<expression, line 1) Below is my expression. I am in ArcDesktop 10.5, trying to concatenate two fields into one. Basically I am trying to create paths that link to where we have Adobe parcel information reports. I have Python selected and I am using a String for my type since this field is a text field. I apologize, I am no expression specialist by any means.

        "\\prodat.local\folders\applications\gisvector\t&m\pdf's\Surveys\12\" + !FMTPRCLNO!.replace(β€œ-β€œ, β€œβ€).replace(β€œ.”, β€œβ€) + ".pdf"

        and I have also tried

        "\\prodat.local\folders\applications\gisvector\t&m\pdf's\Surveys\12\" & !FMTPRCLNO!.replace(β€œ-β€œ, β€œβ€).replace(β€œ.”, β€œβ€) & ".pdf"

        Thanks in advance,
        Bart

      • Ah, it’s your backslashes. Python tries to interpret them, so you get an error. For every backslash that you want to keep, you have to add another to tell Python not to interpret it. So you need to double every backslash up:

        “\\\\prodat.local\\folders\\applications\\gisvector\\t&m\\pdf’s\\Surveys\\12\\” + !FMTPRCLNO!.replace(β€œ-β€œ, β€œβ€).replace(β€œ.”, β€œβ€) + “.pdf”

        Try that. -mike

  9. HELLO MIKE, i am trying to remove some the zeros in the last figures of this expression 100/23/34/0034 . i want the expression to appear as 100/23/34/34. Which expression should i use, THX!

    • Hi Brenda. Do you have just one field with values like “100/23/34/0034” or is there a field that has the last number like “0034”? If it is all in one field, then I would use:

      “/”.join([s.lstrip(“0”) for s in !FIELD_NAME!.split(“/”)])

      mike

  10. Hi Mike,

    I am trying to extract just “NP.117” from a field that contains the following text:
    “Dalje Nga Depo RR. Bekim Berisha Abeja NP.117 UR.Depos 79 Mustafe Haxhijaj Montimi”
    How can I achieve that in field calculator with python?

    Thanks in advance

      • Mike thanks for your reply. There is always value in the field that starts with “NP.” but the numbers change (eg. NP.745, or NP.2850). So I would like to extract these values from the existing field and calculate it to another field.

      • Hi Mesud. The code below will work in the field calculator:

        !FIELD1![!FIELD1!.find("NP."):].split()[0]

        FIELD1 is the field that holds the text that you want to extract the “NP.” data. Also make sure that the field calculator parser is set to Python.

        If you want to know what this code is doing, here is an example by breaking it down in python:

        >>> string = "Dalje Nga Depo RR. Bekim Berisha Abeja NP.117 UR.Depos 79 Mustafe Haxhijaj Montimi"
        >>> string.find("NP.")
        39
        >>> string[39:]
        'NP.117 UR.Depos 79 Mustafe Haxhijaj Montimi'
        >>> string[39:].split()
        ['NP.117', 'UR.Depos', '79', 'Mustafe', 'Haxhijaj', 'Montimi']
        >>> string[39:].split()[0]
        'NP.117'
        >>>

  11. Mike thanks for your reply. The code that you provided worked well, but only after I selected the records that have “NP.” in the field. I noticed that there are records with “np.” occurrence! Is there a way to extract both without having to make a selection in the table?

  12. I have a set of addresses such as:
    206 Air Harbor Rd
    5462 Clearwater Ln
    504 600 N Elm St
    2369 3000 Friendly Av

    Any idea on how to make a selection of all records with the ranges (the last two)?

    • Hi Jennifer. This is a hard one, but you are basically looking for addresses that start with two numbers separated by a space. You can use the Select By Attributes tool and do this as the selection statement:

      (FIELD1 LIKE ‘1%’ OR FIELD1 LIKE ‘2%’ OR FIELD1 LIKE ‘3%’ OR FIELD1 LIKE ‘4%’ OR FIELD1 LIKE ‘5%’ OR FIELD1 LIKE ‘6%’ OR FIELD1 LIKE ‘7%’ OR FIELD1 LIKE ‘8%’ OR FIELD1 LIKE ‘9%’) AND (FIELD1 LIKE ‘% 1%’ OR FIELD1 LIKE ‘% 2%’ OR FIELD1 LIKE ‘% 3%’ OR FIELD1 LIKE ‘% 4%’ OR FIELD1 LIKE ‘% 5%’ OR FIELD1 LIKE ‘% 6%’ OR FIELD1 LIKE ‘% 7%’ OR FIELD1 LIKE ‘% 8%’ OR FIELD1 LIKE ‘% 9%’)

      Note FIELD1 is my field that contains addresses. You would need to modify it with your field name. This basically selects all records that start with a number character from 1 to 9, then any other character after that … AND also the character after the first space must be a number character from 1 to 9, then any other character after that. That statement should select all addresses that have the first two “words” as numbers. -mike

  13. Hi Mike.

    I’m not a expert in this area and I was wondering if you could help me out. I’m trying to calculate a new field in where if the first two letters of another field are “FF” my new field will be named as “Wall Mapping”

    This is what I’m trying to do:

    type = !Name![0:2]
    value1 = “FF”
    if type = value1:
    return 1
    end if

    Not working for me at all.

    Hope you can help.

    • Hi Patricia. Just wondering if selecting all values that start with “FF” in the first field, then calcing the value “Wall Mapping” to the second field based on the selected records would be much easier instead of building a python definition in the field calculator? To select records with a value starting with FF, I would just use this expression in the select by attributes tool in ArcMap: YOURFIELDNAME LIKE ‘FF%’ . Next bring up the attribute table, view the selected records, right click on the second field and bring up the field calculator, then just enter your Wall Mapping value to calc all the selected records.

      • Hi Mike.

        thank you very much for your response.

        These values will change, which is why I would like to keep it as a python definition. I also thought about creating another field with only this expression: !Name![0:2] and then continue with:

        if type = “FF”:
        return 1
        else

        end if

        But I want to do it with just one field.

        Is it possible?

      • Hi Patricia. Anything is possible with Python! So right click on your new field to bring up the field calculator, select Python as your parser, and check the Show Codeblock box. Your code should look something like this in the Pre-Logic Script Code box:

        def TwoLetters(value):
          if value[:2] == "FF":
            return 1
          else:
            return 0
        

        Then below that is another box, you enter your call to the TwoLetters function:

        TwoLetters(!Name!)

        Where your field named Name contains the FF characters. When you click OK on the field calculator, the new field will be given a 1 if the field Name has an FF, and a 0 if not. If you don’t want to return 0, then just remove the else and return 0 statements.

        mike

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s