Useful EXCEL VBA Commands:
Most of these commands work in EXCEL and ArcGIS Field Calculator. ESRI support has a nice description of these commands in the context of the Field Calculator here and some examples here.
- left: The Microsoft Excel LEFT function allows you to extract a substring from a string, starting from the left-most character.
LEFT( text, [number_of_characters] )
- right: The Microsoft Excel RIGHT function extracts a substring from a string starting from the right-most character.
RIGHT( text, [number_of_characters] )
- mid: The Microsoft Excel MID function extracts a substring from a string (starting at any position).
MID( text, start_position, number_of_characters )
- len: The Microsoft Excel LEN function returns the length of the specified string.
LEN( text )
- find – EXCEL: The Microsoft Excel FIND function returns the location of a substring in a string. The search is case-sensitive.
FIND( substring, string, [start_position] )
- instr – ArcGIS (see ESRI links above): Returns a Variant (Long) specifying the position of the first occurrence of one string within another.
InStr([start, ]string1, string2[, compare])
- concatenate – EXCEL: The Microsoft Excel CONCATENATE function allows you to join 2 or more strings together.
CONCATENATE( text1, [ text2, ... text_n ] )
- concatenate – ArcGIS: The ArcGIS CONCATENATE function allows you to join 2 or more strings together. Its syntax, however, is slightly different. Instead of the concatenate key word, use the & to field entries and text.
( [field1] & [field2] & "
" & ... )
- ucase: The Microsoft Excel UCASE function converts a string to all upper-case.
UCase( text )
- lcase: The Microsoft Excel LCASE function converts a string to lower-case.
LCase( text )
- replace – EXCEL: The Microsoft Excel REPLACE function replaces a sequence of characters in a string with another set of characters.
REPLACE( old_text, start, number_of_chars, new_text )
- replace – ArcGIS: The ArcGIS REPLACE function replaces a sequence of characters in a string with another set of characters.
REPLACE( old_text or [field], text_to_replace, new_text )