VBA Magic – GISNOTE

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] & "
```
text
```
" & ...  )
```
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 )
```