Re: Number and Currency 'Pretty-Print' Routines
tl;dr version: A demonstration base offering three approaches to formatting numbers or numerals while providing ‘pretty-print’ formatting (e.g., currency indicators, thousands and decimal separators, and so on). Allows for non-US separators and decimals, as well. The third table supports parentheses for negative values, right-aligned output with a specified number of columns, and more.
A while back, in response to this query, I started on what I thought would be no more than a half-hour’s task of hunting down some code I’d written, giving it a quick polish, and slapping together a reply.
Two months later…
Here is a demonstration base that includes ‘pretty-print’ routines for long numbers and currency amounts. As you may have already discovered, when converting a number to a string — for instance, when concatenating it with explanatory text, or when combining fields to make better use of gallery or kanban screen space — it loses any field formatting that may have been applied. For example, here is a snippet from my Sales CRM Dashboard base showing a couple of composite fields I use in a gallery view. The image on the left is how I wanted the composite field to look; the one on the right is how they looked using Airtable-standard number-to-string conversion:
(Note: Traditionally, the transformation of data into a more-user-friendly format is known as ‘pretty-printing’ — even though the teletype ceased to be the primary computer interface 40+ years ago. It can be a highly complex problem: It can be argued that much of the 32-year gap between volumes 3 and 4 of Donald Knuth’s The Art of Computer Programming stems from a pretty-print issue that led to Knuth setting aside his monograph for several decades while he perfected the TeX formatting language. Despite the risk [probability?] of sounding silly, I continue to use ‘pretty-print’ as an economical, unambiguous description of such processing.)
When @daybreak asked how to retain the comma in a converted string, I first thought I would simply point her at the pretty-print routines I’d used in the CRM Dashboard. Then I looked at them. They are, in a word, hideous; I suppose once I found something that worked, no matter how inelegant, I moved on to more interesting parts of the base. I decided to start from scratch.
I soon realized there were actually two use cases to address here: The first was the original problem of taking a numeric value in Airtable and converting it to a string using a more user-friendly format. The second was taking inconsistent and possibly malformed user input and normalizing it to a standard format. Accordingly, the demo base includes two formulas for pretty-printing, one that takes as its input a number, the other starts with a string. In addition, I put together a more robust algorithm that supports a number of additional formatting options (left- versus right-alignment; currency or not; parenthesis versus minus sign to indicate negative numbers; and so on).
Caveats: Since the original question dealt specifically with formatting currency amounts, the two major routines both begin the string with a currency indicator (or, in the case of negative values, a hyphen-minus followed by a currency indicator), and they both enforce two digits following the decimal point. (I realize for some this is burying the lede, but these routines can easily be modified to use non-US thousands and decimal separators.) Also stemming from their origin as currency-formatting routines, as provided they support values between 0.00 and 999,999,999.99; anyone building a base to manage the [US] federal budget will need to modify the code to support (short-scale) trillions.
The base includes four tables: [Currency Demo], [String], [Number], and [Bells and Whistles]: [String] and [Number] contain the formulas to format, respectively, strings and numbers (clever mnemonics, no?); [Bells and Whistles] provides an interactive demonstration of the enhanced formatting routines; and [Currency Demo] brings everything together on one display, as so:
-
{StringValue}contains strings, some malformed, as input to the routines in the[String]table. -
{NumberValue}is simply{StringValue}wrapped in aVALUE()function; you can see how Airtable interprets certain malformed numerals.[1] Here I have formatted the resulting values as currency, primarily to demonstrate how such formatting is lost when Airtable converts the field to a string. -
{Airtable-Standard}illustrates Airtable’s standard number-to-string conversion. Note how the currency indicator, thousands separator, and forced decimal points are all lost. -
{from String table}and{from Number table}are simply lookups of the formatted output of the two sets of routines, demonstrating the return of ‘pretty-print’ formatting to the numeric values and the standardization and normalization of string values. - Finally,
{from B&W}and{B&W Settings}display, respectively, the output from the ‘bells and whistles’ routines and the current configuration of each field. (More later.)
The basic string- and number-formatting routines can be found in the respective tables; the formulas are relatively straightforward, and the most likely modifications (namely, removing or modifying the currency indicator or thousands and decimal separators) should be easy to make. That said, the [String] table formula is undoubtedly the ugliest piece of Airtable code I have ever seen, let alone written — and the [Number] routine isn’t much better. (The string-formatting routine acquires much complexity from 1) having repeatedly to strip out minus and dollar signs and commas, and 2) having to cope with numerals with more than two significant digits.) If you need help modifying the routines, please ask — but better do it soon, before I forget how they work!
Bells and Whistles
The [Bells and Whistles] table provides routines for enhanced string formatting and an interactive demo of this functionality. It takes as its input a string value[2] and outputs a string according to a number of configurable options, as shown below:
- [R]ight or [L]eft alignment
- Parentheses or
hyphen-minusto indicate negative values. - Currency or not
- Number of columns to the left of the decimal point (only meaningful for right-aligned text, and column number does not take into consideration spacing for parentheses,
hyphen-minus, or currency indicators) - Forced decimal (and two significant digits) or not
Each of these values can be changed in the grid view, with the output string automatically reformatted accordingly. The final displayed field, {Settings}, shows the current output configuration.
Unlike those created by the [String] and [Number] routines, [Bells and Whistles]-formatted strings right-align properly, regardless whether they are positive or negative values.
In practice, this code would not ordinarily be used in an interactive manner; instead, the routines would be adopted with configuration options hard-coded.
Note: The formula uses a variety of fixed-width space characters to pad various format combinations to match. Accordingly, as delivered they depend heavily on character widths intrinsic to the current Windows system font, Segoe UI. They have not been thoroughly tested under macOS or Linux, and users with a non-standard systems font may see some degradation. Modifying the currency indicator or the thousands or decimal separators may also throw off padding. There is a hidden field defined in the [Bells and Whistles] table, {Spaces}, that provides an example of each fixed-width space type.
As it seemed that every time I thought I was ready to release this, I found another exception or failure, it is very likely this version is not bug-free. Should you find one (or more), please let me know, and I’ll see if I can provide a fix.
Edit: It occurred to me I left a potentially confusing-looking OR() statement in the [Bells and Whistles] table: At first glance it appears I check twice to see if the first character in the numeric string is equal to ‘-’. Actually, the first time I check to see if it matches ‘-’, the hyphen-minus character (U+002D), and the second time ‘−’, the minus sign character (U+2212). As a graphic artist in an earlier life, I prefer (in most fonts that support it) the look of the latter, and early versions of this base used minus sign to indicate negative values. Unfortunately, Airtable’s VALUE() function currently only recognizes hyphen-minus or parentheses as negative indicators; to avoid confusion should anyone have to convert a pretty-printed string back to a number, I standardized on hyphen-minus. If string->number conversion is not a concern, feel free to use either form of the minus sign.
. __________
- Personally, I believe Airtable’s valuation of ‘96t7 4B.32’ as ‘9674.32’ should be considered a bug. I would expect
VALUE(string)wherestringis not a valid numeral to return#ERROR; instead, Airtable simply ignores all non-numeric characters and calculates a value based solely upon the remaining digits. I hope to see this corrected in a future release. - To use a number field with the
[Bells and Whistles]routines, simply append ‘&''’ to the numeric field name to cast it as a string.
I have a crazy weird one. As per the sceenshot, doing invoices.
- invoiceAmount is a currency field, and is entered with an automation (it does not matter if I change it or enter it manually though).
- paid (formatted as a currency) is a rollup of the amount field (currency) for the attached invoicePayments.
- invoiceBalance is a formula (invoiceAmount-paid)
- invoiceBalancePretty I am using the formula from the number (table) -> pretty (field) from the base attached to this thread.
Weirdly, three of these have wigged out, 3 of 715. That 5.68 is also not 5.68, but when I print it, becomes 5.65....e-14 (so this is some crazy fraction of 1).
Any thoughts?
