Thursday, December 10, 2009

String Functions for ArcMap Field Calculator

Back in August I posted an entry on a suite of Python scripts that I wrote for altering strings in the ArcMap attribute table. This included scripts for capitalizing words, converting lower case to upper case and vice versa, trimming white spaces, and stripping characters from the beginning and end of strings. Click here to read that entry titled Batch Edit String Fields – Free Tools for Calculating String Fields and to download the toolbox and the corresponding scripts.

While the Python scripts have been very popular among followers of Geospatial Analyst, I’ve noticed that a lot of the traffic on my blog originates from people searching for the correct VB syntax to alter string attributes. Many people are just looking for the syntax to quickly alter a string field, instead of downloading and using the Python scripts that I’ve made available. So I’ve decided to write this entry on VB field calculator expressions for common string manipulations.

***The first thing to note: Many people mistakenly search for the syntax for LTrim() and RTrim() functions when they want to strip characters from the beginning or end of a string. However, the LTrim() and RTrim() functions simply remove preceding and trailing white spaces, respectively, from the beginning and end of the string. To strip characters (instead of white spaces) from a string, you must use the Right(), Left(), or Mid() functions.***

Listed below are expressions to plug into the field calculator along with the resulting output for the string “Hello Fantastic World!”.



Input: Hello Fantastic World!
Expression: Left( [FieldName], 5)
Output: Hello
Explanation: Returns the first 5 characters from the left.


Input: Hello Fantastic World!
Expression: Left( [FieldName], Len( [FieldName] ) -17)
Output: Hello
Explanation: Returns the string minus the last 17 characters.


Input:
Hello Fantastic World!
Expression: Right( [FieldName], 16)
Output: Fantastic World!
Explanation: Returns the last 16 characters from the right.


Input: Hello Fantastic World!
Expression: Right( [FieldName], Len( [FieldName]) -6)
Output: Fantastic World!
Explanation: Returns the string minus the first 6 characters.


Input: Hello Fantastic World!
Expression: Replace( [FieldName], "Fantastic", "Beautiful")
Output: Hello Beautiful World!
Explanation: Replaces the first string with the second string.


Input:
Hello Fantastic World!
Expression: Mid( [FieldName], 7, 9)
Output: Fantastic
Explanation: Returns the first nine characters beginning at the 7th character.


Input:
Hello Fantastic World!
Expression: Mid( [FieldName], 17, 5)
Output: World
Explanation: Returns the first 5 characters starting at the 17th character.


Input: Hello Fantastic World!
Expression: UCase( [FieldName] )
Output: HELLO FANTASTIC WORLD!
Explanation: Returns the entire string in upper case.


Input:
Hello Fantastic World!
Expression: LCase( [FieldName] )
Output: hello fantastic world!
Explanation: Returns the entire string in lower case.

17 comments:

  1. I know you can convert numbers to strings within the field calculator, but what if I want to do the reverse? I have a string field populated with some numbers and some text. I want to transfer that data to another field, keeping the the actual numbers and entering the rest of the records with text as zeros in the new field. Is this as complicated as I think??

    ReplyDelete
  2. Being in a GIS course at college, this page was hugely helpful in figuring out how SQL works!

    ReplyDelete
  3. Thanks for this, productivity just increased!

    ReplyDelete
  4. Oh thank you! You explained so nicely that I could understand! No more guess work and tryout errors:)

    ReplyDelete
  5. What are the equivalent Python functions for Left, Right, and Mid?

    ReplyDelete
  6. Thanks, Brooke! Big help working to standardize my parcel data.

    ReplyDelete
  7. Hello fantastic blog ;)
    very helpful informations, thanks Brooke!
    greetings from Poland,
    Chris

    ReplyDelete
  8. How can I get the first capital letter in ArcGIS?

    ReplyDelete
  9. Hi. There are a few different ways of doing this:

    1. You can use the Capitalize tool from the Batch Edit String tools: http://www.geospatialanalyst.com/2009/08/batch-edit-string-fields-tools-for.html
    2. You can use VBScript in the Field Calculator: UCase(Left([FieldName], 1)) + Mid([FieldName], 2)
    3. You can use Python in the Field Calculator: !FieldName!.capitalize()

    Hope that helps,
    Brooke

    ReplyDelete
  10. You saved my day!!! thanks!!

    ReplyDelete
  11. this is great - any way to pull everything from a string before the first comma? For instance I want to pull just soil types - and these can range in character numbers - but they always have a comma at the end of the type. So if I could write some code that would tell it to pull until the first comma that would be great!

    ReplyDelete
    Replies
    1. There are actually a couple ways I can think of to do this, one using regular expressions, the other using the find method. The find method is the much simpler method if you're not already familiar with regular expressions. Here's an example:

      soil_types = "xyz,abc,lmn"
      print soil_types[0:soil_types.find(",")]

      >>> 'xyz'

      In this case the find method returns the position of the first comma in the string. Then you can use the brackets [] to print only a subset of characters in the string, in this case beginning with the position of the first character (0), and end with the last character before the comma (soil_types.find(",")).

      Hope that helps!

      Delete
  12. hey there, I need to just take out the symbol " from my field. I have 12"x12" I dont' want the inches symbol there, tried mid, .strip and other ideas...nothing is working.

    thanks so much!

    cartogal

    ReplyDelete
    Replies
    1. thought i would answer my own question since I found the answer and someone else might need it.... I just used the find and replace tool, didn't know Arc had one. In the table view, highlight the field you want to edit than click on table options, at the very top of the list there should be the find and replace tool!

      Delete
  13. Can you use a variable in place of the "-6" shown in the variable below?

    Input: Hello Fantastic World!
    Expression: Right( [FieldName], Len( [FieldName]) -6)
    Output: Fantastic World!
    Explanation: Returns the string minus the first 6 characters.

    ReplyDelete