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

36 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

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s