It is also possible to write a formula in Emacs Lisp. This can be useful for string manipulation and control structures, if Calc’s functionality is not enough.
A formula is evaluated as a Lisp form when it starts with a
single-quote followed by an opening parenthesis. Cell table
references are interpolated into the Lisp form before execution. The
evaluation should return either a string or a number. Evaluation
modes and a printf format used to render the returned values can be
specified after a semicolon.
By default, references are interpolated as literal Lisp strings: the field content is replaced in the Lisp form stripped of leading and trailing white space and surrounded in double-quotes. For example:
'(concat $1 $2)
concatenates the content of columns 1 and column 2.
When the ‘N’ flag is used, all referenced elements are parsed as numbers and interpolated as Lisp numbers, without quotes. Fields that cannot be parsed as numbers are interpolated as zeros. For example:
'(+ $1 $2);N
adds columns 1 and 2, equivalent to Calc’s ‘$1+$2’. Ranges are inserted as space-separated fields, so they can be embedded in list or vector syntax. For example:
'(apply '+ '($1..$4));N
computes the sum of columns 1 to 4, like Calc’s ‘vsum($1..$4)’.
When the ‘L’ flag is used, all fields are interpolated literally: the cell content is replaced in the Lisp form stripped of leading and trailing white space and without quotes. If a reference is intended to be interpreted as a string by the Lisp form, the reference operator itself should be enclosed in double-quotes, like ‘"$3"’. The ‘L’ flag is useful when strings and numbers are used in the same Lisp form. For example:
'(substring "$1" $2 $3);L
extracts the part of the string in column 1 between the character positions specified in the integers in column 2 and 3 and it is easier to read than the equivalent:
'(substring $1 (string-to-number $2) (string-to-number $3))
When the formula itself contains ‘;’ symbol, Org mode may incorrectly interpret everything past ‘;’ as format specifier:
'(concat $1 ";")
You can put an extra tailing ‘;’ to indicate that all the earlier instances of ‘;’ belong to the formula itself:
'(concat $1 ";");