Using the Field Calculator to edit Strings in the Attribute Table
The Field Calculator provides GIS users with a powerful way to efficiently edit attribute table field values. It helps to eliminate tedious manual edits through the use of VBA Script Functions and Python Functions. Even users not proficient in Python scripting are able to harness the power of the Field Calculator to quickly edit and enhance their data.
Here we will quickly review what some of the most common VBA functions are and how they can be used to expedite your attribute manipulations.
First, what are VB Script functions? These are Visual Basic functions and can be used to edit number, string (text) or date data fields in conjunction with various expressions within the Field Calculator.
By selecting the appropriate data type (see above), you open up access to functions that can be used to edit or enhance your data. Here we will review some of the commonly used VB script functions to be used with strings.
First, let’s explore the how to determine how many characters make up a string. This will result in a long integer that will tell you the number of characters making up at string. Below, see an example of how this can be used:
When you have finished entering your equation, click OK and view the results in your attribute table. The “Test” field contains the results of our calculation.
Next, we will explore the Left and Right functions. When used, these will return the specified number of characters from either the left or the right (depending on which function has been used). This can be useful in situations where it is necessary to split up fields. Below, we will work on splitting the START time for a bus route away from the END time for a bus route.
When you have finished entering your equation and click OK, you will get the following result.
Similarly, the Right function can be used to recall a specified number of characters from the RIGHT of the attribute field. Here we will remove all of the END route times. Notice below that we are returning the 5 rightmost characters; we are doing this so we can return all entries that include a 4-digit time and the ‘p’ designating a PM time.
Now, we need to address the issue of removing the space from those attributes that do not contain 5 characters. Until we do this, we will be unable to successfully remove the leftmost characters with the left function.
To accomplish this, we will first sort our Test field “ascending” and select all entries that have less than 5 characters (3-digit time and the ‘p’ designating PM). We will then use the Trim function. This function only removes spaces from the beginning or ending of a text field. This is done by selecting the LTrim( [textfield])function, and the text field from which the spaces will be trimmed from the left of the field. Below, see the code and the result.
Now, the Left function can be used to return these times.
There are a variety of other string functions available for use. For a complete list of string functions and also complete lists of number and data functions, please review this Esri article on the Field Calculator.