I found the TrimLeft and other string functions within the formulas module to be quite helpful for this exercise.
Handled the last 2 challenges with a single formula using conditional logic commands.
- Convert input to number using ToNumber function
- Convert input to number using ToNumber function (this automatically removed all text characters - but did have to resize to INT32 to hold larger number)
- Remove "ID" from right 2 characters of field (if it exists)
- Calculate string length
-- If = 6 append "SC" to front
-- If >8 grab left-most 8 characters
-- Otherwise, no change
Pleasant surprise was converting string to number removed text automatically.
- converting string
- be careful about length of new field (can be huge accidentally)