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.