# org table formula throws error when it depends on another formula

In my training center, i have the students name put into a table. And I like to parse the names into first, middle and last names. AFAIK, there is no `indexOf` string function in elisp. So, I thought of using `string-match` to get the space indices, and then use `substring` method.

``````| Name           | Space1 | Space2 |
|----------------+--------+--------|
| T Rama Rao     |      1 | #ERROR |
| Va Venkata Rao |      2 | #ERROR |
#+TBLFM: \$2='(string-match " " \$1)::\$3='(string-match " " \$1 (1+ \$2))
``````

That dependent formula is not picking the calculated values and thus, the column is filled with errors.
I tried `org-table-recalculate ( C-c * ) ` & `org-table-iterate (C-u C-u C-c *)`. There is no visible improvement with these methods. In fact, they converted the formulas into a header line by adding a star at the line's front for no clear reasons.

Now, i went for another method.

``````| Name           | First  | Middle | Last   |
|----------------+--------+--------+--------|
| T Rama Rao     | #ERROR | #ERROR | #ERROR |
| Va Venkata Rao | #ERROR | #ERROR | #ERROR |
#+TBLFM: @2\$2..@>\$>='(split-string \$1 " ")
``````

Since the `split-string` is expected to return a list with three elements, I am trying to assign it to three cells. Now, it puts errors in all cells.

Unless you are really worried about performance, I would simplify to this:

``````* Table

| Name           | First | Middle  | Last |
|----------------+-------+---------+------|
| Ta Rama Rao    | Ta    | Rama    | Rao  |
| Va Venkata Rao | Va    | Venkata | Rao  |
#+TBLFM: \$2 = '(first \$1) :: \$3 = '(middle \$1) :: \$4 = '(last \$1)

* Code
#+begin_src elisp
(defun first (name)
(nth 0 (split-string name)))
(defun middle (name)
(nth 1 (split-string name)))
(defun last (name)
(nth 2 (split-string name)))
#+end_src
``````

You end up calling `split-string` three times for each name, but for a few names (or even a few tens of names), that shouldn't make much difference.

In general, it's better IMO to simplify the table formula(s) as much as possible by putting all the complications in Lisp functions that can be called simply. The spreadsheet in Org is a bit peculiar and although you can do complicated stuff (e.g. transpose a table), the formulas are hard to understand.

Once you have a working table, you can perhaps try to be "clever", e.g. you can combine the three formulas into one range formula:

``````
* Table

| Name           | First | Middle  | Last |
|----------------+-------+---------+------|
| Ta Rama Rao    | Ta    | Rama    | Rao  |
| Va Venkata Rao | Va    | Venkata | Rao  |
#+TBLFM: @2\$2..@>\$> = '(which \$1 \$#)

* Code

#+begin_src elisp
(defun which (name col)
(nth (- col 2) (split-string name)))
#+end_src

#+RESULTS:
: which

``````

`\$#` is the column number for the current column, so we offset it by 2 in order to calculate the index of the element of the list.

But we are still calling `split-string` three times for every name, so it's not clear that the brevity is worth the loss in readability. Ultimately, you'll have to decide where to put your efforts. IMO, the simpler it is, the better.

• skipping row indexes in table formula `\$2..\$>='(which \$1 \$#)` didn't work. however, `@2\$2..@>\$>='(which \$1 \$#)` works... Mar 16 at 17:00
• Fixed - thanks! Mar 16 at 17:41

Thanks to this video, I learned one or two tricks about org-table formula debugger. You can invoke `org-table-toggle-formula-debugger` through `C-c {` when there are any `#ERROR` values.

And I can see, `(string-match " " \$1 (1+ \$2))` is calculated as `(string-match " " \$1 (1+ "2"))` and thus, adding a number to string causes error.

Casting the string to number does the trick as, `\$3='(string-match " " \$1 (1+ (string-to-number \$2)))`

Another shorter solution is to use calc mode's L flag that forces non-quoted variables to be treated as numbers. So, the simpler solution is, `\$3='(string-match " " "\$1" (1+ \$2));L`

The resulting table looks like this,

``````| Name           | Space1 | Space2 | First | Middle  | Last |
|----------------+--------+--------+-------+---------+------|
| T Rama Rao     |      1 |      6 | T     | Rama    | Rao  |
| Va Venkata Rao |      2 |     10 | Va    | Venkata | Rao  |
#+TBLFM: \$2='(string-match " " \$1)::\$3='(string-match " " "\$1" (1+ \$2));L::\$4='(substring "\$1" 0 \$2);L::\$5='(substring "\$1" (1+ \$2)  \$3);L
#+TBLFM: \$6='(substring "\$1" (1+ \$3) nil);L
``````