Formulas: ToNumber() and ToText()
Converts a value into a number
or stated otherwise...
Hey Coda, I need this value to be a different value type. Will you change it to be a number value?
Converts a value into text
put a bit simpler...
Hey Coda, I need this value to be a different value type. Will you change it to be a text value?
ToNumber() and ToText() each share one parameter:
The value to convert to text.
Technically, ToNumber() has an additional, optional parameter— Base — that changes the base number. Nearly every numerical system uses a base or “radix”—the number of unique units, including zero. Most numerical systems use a base of 10. You will rarely, if ever use this parameter.
Ben Lee, a longtime Codan, once shared the following:
There’s an inside joke at Coda, which is “Did you try ToText()?”
It is Coda’s version for “Did you turn it off and back on”.
This sentiment perfectly captures just why you will be using ToText() regularly and extensively. Especially when starting out in Coda, you will often encounter two rows which look like a match, and yet do not deliver the intended result.
Let’s look at an example:
As a school administrator, you want to display a checkbox for every student who has been asked to give a speech at the school’s award ceremony. You use a filter-formula do to so:
You are surprised to discover that your search does not reveal any matches. Simply by looking at the tables you can see that three matches should appear.
In most cases, the reason for matches not showing up expected lies in differing data types. That is, the two parameters you are trying to compare do not return a match, because they aren’t an exact match (ie do not share the same data type).
Enter to the rescue: ToText() and ToNumber()
These formulas do exactly what their names indicate—they convert whichever input you give them into a text or number datatype, respectively.
For the example stated above, applying ToText() is a quick and easy way to investigate why you are not finding any matches, thereby allowing you to rule out reasons other than mismatched data types.
Try it out: In Table “All Students” above, edit the formula in the column “Topic of Speech” by adding to suffix .ToText() to the parameter Participant. And, just like that, now the matches do appear as expected (see excerpt on the right).
Common Misuse of ToText()
You would be wise to avoid the habit of converting row reference to text solely for the purpose of receiving a match and leaving it that way, or for having formula sequences output as text. Once you convert data to text, you lose all references to the underlying data.
ToNumber() has the same effect, albeit for—you guessed it—numbers. Especially when working with Data which has been imported from a different data source into Coda, the auto-detection of column types is thrown off by localization formatting (ie country-specific formatting which might differ from US-default settings).
Without applying ToNumber()