Trim() followed by ToNumber() returns the wrong result

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
image

This behavior occurs because of several facts:

  • how 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.
4 Likes