My objective is to have a table column that can support both dollar amounts or percentages. I do this by having a text field which I parse. This works great but there is unexpected result if I trim the text.
Trim("9% ").toNumber() results in 9
ToNumber("9%") results in 9%
See this doc that demonstrates the problem.
ToNumber("9%") is still 9
This behavior occurs because of several facts:
ToNumber() works with text values: extracts the first set of digits it may find and returns those as a numeric, otherwise just returns the input.
how Coda tries to type-guess what you’re entering into a cell.
When you enter 9% without a trailing space in your “Expected Behavior” table, Coda treats it not as a text value but a percent value already. You can see the evidence here:
If you force it to text type and then try ToNumber on it, you’ll get 9
The best solution would be to
- Check the value for
.IsNumber() and not do any conversion if it’s that.
- If it’s not number, trim it and check if it has a percent sign in it. Based on that divide the value by 100 or not.