How to prevent digit loss in orgmode spreadsheets with elisp?

Question

That's probably a low hanging fruit.

Assume an orgmode spreadsheet with one side for input - a number - and the other side for output. The other side is specified with simple elisp code, taking the input string from the one side, interprets it as a number, and copies it on the output side.

|----------------+------------------------|
| Input          | Output                 |
|----------------+------------------------|
| certain number | copy of certain number |
|----------------+------------------------|
#+TBLFM: @2$2='(string-to-number @2$1)

If input is 428.94, everything is fine.

|--------+--------|
|  Input | Output |
|--------+--------|
| 428.94 | 428.94 |
|--------+--------|
#+TBLFM: @2$2='(string-to-number @2$1)

But if input has a zero as the last digit - like, for example 597.50 - the number gets shortened by that last zero digit. The result: Input had 5 digits, Output just 4, not identical anymore.

|--------+--------|
|  Input | Output |
|--------+--------|
| 597.50 |  597.5 |
|--------+--------|
#+TBLFM: @2$2='(string-to-number @2$1)

Certainly there is a simple way to extend string-to-number by a format building block, which makes sure, that the interpretation of the input string always has just as much digits after the decimal separator - in the example 2 digits - no matter if the string contains something like ".07" or something like ".20".

|--------+--------|
|  Input | Output |
|--------+--------|
| 597.50 | 597.50 |
|--------+--------|
#+TBLFM: @2$2='???(string-to-number @2$1)???

What does the trick?

Answer 1

See 3.5.2 Formula syntax for Calc of the Org Mode manual. It’s not called out specifically (it just says that you can use a printf format specifier), but several of the examples show how it works. You just have to add ;%.2f after the formula.

Answer 2

db48x's answer turns out right - in simple cases like the ones introduced. But when it gets more complex with string-to-number- and number-to-string-switches, the format specifier can fall short, losing access to where the damage is done, and thus come too late in processing.

My true case was just like that, but I just came up with a great idea by myself:

Another way of making sure that output format always has 2 digits after the decimal separator is building a help construction, which adds a 0 at the end of the output string, if the said output string ends with just 1 digit after decimal separator. I for my case just had to wrap this here around my complex computation for the desired result:

(replace-regexp-in-string "\\(,[0-9]$\\)" "\\10")
In extended table context:
@2$2='(replace-regexp-in-string "\\(,[0-9]$\\)" "\\10" (complex computation))

UPDATE: Now I know better! A fundamentally different, general approach is more efficient:

1st: Turn , into . - the separator natuarlly digested by orgmode spreadsheets - doing so ensures you using that tool in line with its natural design, instead of trying to pee against the wind

2nd: Do the usual orgmode-spreadsheet processing - either with simple TBLFMs, or elisp TBLFMs

3nd: Turn . back into ,

Such a segregation of custom formatting and processing - custom formatting as a separate frame of default processing - is much more efficient: Cleaner - more straight-forward - more transparent - easier to understand - and maintaining suitability with increasing amount of data. So, you want custom formatting? Fine, then do it before and afterwards, but not mixed with processing. The before part must prepare the data back to comply with default processing and afterwards, the second custom formatting part, you can do whatever you want with the results - changing , to flowers, stars, ######, whatever.