Help

Re: Number and Currency 'Pretty-Print' Routines

8386 1
cancel
Showing results for 
Search instead for 
Did you mean: 

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:
composite_fields_5
(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:
base_note

  • {StringValue} contains strings, some malformed, as input to the routines in the [String] table.
  • {NumberValue} is simply {StringValue} wrapped in a VALUE() 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:
b%26w

  • [R]ight or [L]eft alignment
  • Parentheses or hyphen-minus to 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.
. __________

  1. Personally, I believe Airtable’s valuation of ‘96t7 4B.32’ as ‘9674.32’ should be considered a bug. I would expect VALUE(string) where string is 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.
  2. To use a number field with the [Bells and Whistles] routines, simply append ‘&''’ to the numeric field name to cast it as a string.
19 Replies 19

Thanks @kuovonne I will check it out! As state government agency, we often have to do the hard way over the pay way!

You might want to take a look at https://github.com/UnlyEd/airtable-utils then, might help you out! (and I’d love to get some PRs if you have formulas to share :winking_face: )

Tim_Heymans
6 - Interface Innovator
6 - Interface Innovator

Do I understand correctly that if I want to have “pretty print” numbers, I always have to have duplicate columns?
I.e. one with the “raw” numbers and one with the “pretty print” ones?

Example: I want to transform a Total Hours field (numbers; not duration) into Work Days and then Work Days into Weeks according to certain criteria specified by me. For example: 95 hours = 24 workday; 24 workdays = 6 weeks.

In excel I can do this with 3 columns, because there I can custom format the numbers and still make calculations with the data…
But… In Airtable I would need 6 columns for this??

Not necessarily. What you’re doing isn’t what I’d call “pretty print.” The “pretty print” routines outlined above are for adding punctuation and such to a “raw” number—e.g. turning 1234567 into 1,234,567.

What you describe is just a normal calculation. That can be done using the same column count that you would use in Excel: your raw number of hours is in one field, that number converted to workdays is in a second field, and that workday number converted into weeks would be in a third field.

Dan_Billin
6 - Interface Innovator
6 - Interface Innovator

Thank you, W_Vann_Hall! This helped me create a formula field that results in either currency-formatted numbers (where they exist in a separate number field) or default text (when the number field is empty). Example: either “$250,000” or “amount not specified”. I had to use a two-stage process: 1) create a field that pulls the numbers as an unformatted string (the formula for the new field simply names the number field); then 2) create a second new field that uses your formula to format the string of numbers, with an IF statement to supply the text where necessary. Thanks again for helping me solve a knotty problem that was way beyond my current ability.

Steve_Cooke
6 - Interface Innovator
6 - Interface Innovator

Thanks v much @W_Vann_Hall for all the work you did on this! 

FONE
4 - Data Explorer
4 - Data Explorer

Wow what a legend.
Thanks for your help !

ca6pma
4 - Data Explorer
4 - Data Explorer

Dear all, I've tried to figure out what everyone just seems to get from this but I can't..

I'm trying to use . as thousand separator and , as decimal separator.

So I'm trying to convert the numbers in my base from e.g 30,500.55 to 30.500,55.

I use currency fields with the symbol of EURO.

I downloaded the sample base but I cannot find any example which does that to copy the respective formula. Everything seems to be using , as thousand separator and . as decimal separator.

Can you please help me?

I figured it out.

Basically I just replaced any reference on ',' with '.' and vice versa.

And also changed the $ to the € symbol.

Thanks for creating that formula!

Scott_EH
4 - Data Explorer
4 - Data Explorer

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.

Scott_EH_0-1687234500540.png

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?