5

I often paste org-mode tables numbers that are e.g. space-separated. Then I have to delete spaces before things like =$2/2 work correctly. Is there a way to change "number format", e.g. plug in a general function to parse input numbers in org-mode tables?

As an example, I'd like

| 100 000 | 0 |
#+TBLFM: $2=$1/2

to give 50000, not 0, in column two (I don't care that the output is not space-separated).

I know about ;N, but that doesn't work either:

| 100 000 | 50 |
#+TBLFM: $2=$1/2;N

and I'd rather have something that works on the full table because I'm sure to forget to put it on each formula.

Improve this question
2
  • Please clarify: do you want to use " " as separator rather than the normal "|" ?
    – Vladimir Alexiev
    Commented Apr 25, 2018 at 9:10
  • No, see my edit :)
    – unhammer
    Commented Apr 25, 2018 at 9:22

2 Answers 2

Reset to default
4

Just use a little lisp to transform the cell string to a valid number.

You can use a lisp form as cell formula if you start the formula with apostrophe and parenthesis '(.

Org-table cell references within lisp forms are resolved as strings by default. If you can add another column to your table you can use a lisp form as simple and self-explanatory as (cl-remove ?\s $1). There ?\s is the space character which is removed from the cell string "100 000" of cell $1. The resulting string is inserted in the target column $2. You can process the result in further columns.

If you really want the result in the second column you must transform the compressed string into a number and process it in lisp.

The first table in the following example uses the easy lisp part the second table uses the variant with only two columns.

| 100 000 | 100000 |  50000 |
| 250 842 | 250842 | 125421 |
#+TBLFM: $2='(cl-remove ?\s $1)::$3=$2/2

| 100 000 |  50000 |
| 250 842 | 125421 |
#+TBLFM: $2='(/ (string-to-number (cl-remove ?\s $1)) 2)
Improve this answer
3
  • nice! I often want to use , for legibility, now I can. Doesn't work for leading dollar signs though - do you know how to parse numbers like $100,000 in org tables?
    – Tyler
    Commented Apr 27, 2018 at 14:05
  • @Tyler That's pityingly emacs.stackexchange.com/questions/15076/…. It happens in the substitution phase before the evaluation phase.
    – Tobias
    Commented Apr 27, 2018 at 14:32
  • hm, is there a way to apply a filter like that to more formulas, or shorten it a bit? I mean, it works, but it'll get quite verbose with several formulas …
    – unhammer
    Commented Apr 27, 2018 at 21:23
-1

org table cells are not limited to a single number. So you cannot use spaces as decimal separator (who does that?)

If these come from excel, you can adjust the format to use no decimal separator.

Improve this answer
1
  • 1
    Space is the character used as thousand separator in Swedish. 1 000 000 is a million with Swedish notation.
    – Rovanion
    Commented Sep 25, 2019 at 6:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.