Mar 24, 2018 07:00 PM
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 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.{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!
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:
hyphen-minus
to indicate negative values.hyphen-minus
, or currency indicators)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.
. __________
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.[Bells and Whistles]
routines, simply append ‘&''
’ to the numeric field name to cast it as a string.Dec 28, 2018 01:37 PM
This is great, @W_Vann_Hall. I was able to copy what you did in the demonstration base and get the comma separators I needed into my currency amounts. Thank you.
Apr 21, 2020 01:45 AM
This is VERY helpful - thanks for sharing this! I have one question: Is it possible to get the "" sign to the right of the string (ie. 1,454.50 )? This would accomplish something Airtable users have been asking for since 2017! Many thanks for your assistance, Johannes
Apr 21, 2020 07:46 AM
Welcome to the community, @Johannes_Schwaninger! :grinning_face_with_big_eyes: I don’t see anything inside the quotes you typed. Perhaps you typed a special character of some kind? Because we can’t see it, could you describe what that character is?
Apr 21, 2020 10:34 AM
Hi @Justin_Barrett , thank you for getting back. I used the special character for Euro, it could also be “Eur” instead.
May 10, 2021 09:42 AM
Hello, I was wondering how the ROUND() formula is being used in this case. The “Pretty” formula solved 99.99% of my issues but the last thing I would like to do is round to the nearest dollar (no decimal points – so $1.15 would ideally just look like $1) but I can’t quite workout what is happening in the formula. Any tips?
IF(
LEFT({NumberValue}&’’,1,1)=’-’,
‘-’,
‘’)&’$’&
IF(
ABS(VALUE({NumberValue}&’’))>=1000000,
INT(ABS(VALUE({NumberValue}&’’))/1000000)&’,’&
IF(
INT(MOD(VALUE({NumberValue}&’’),1000000)/1000)<100,
REPT(‘0’,3-LEN(INT(MOD(VALUE({NumberValue}&’’),1000000)/1000)&’’)),
‘’)&INT(MOD(VALUE({NumberValue}&’’),1000000)/1000)&’,’&
IF(
INT(MOD(VALUE({NumberValue}&’’),1000))<100,
REPT(‘0’,3-LEN(INT(MOD(VALUE({NumberValue}&’’),1000))&’’)),
‘’)&INT(MOD(VALUE({NumberValue}&’’),1000)),
IF(
ABS(VALUE({NumberValue}&’’))>=1000,
INT(ABS(VALUE({NumberValue}&’’))/1000)&’,’&
IF(
INT(MOD(VALUE({NumberValue}&’’),1000))<100,
REPT(‘0’,3-LEN(INT(MOD(VALUE({NumberValue}&’’),1000))&’’)),
‘’)&INT(MOD(VALUE({NumberValue}&’’),1000)),
INT(ABS(VALUE({NumberValue}&’’)))))&’.’&
IF(
LEN(ROUND(MOD(VALUE({NumberValue}&’’),1)*100,0)&’’)<2,
‘0’,
‘’
)&
ROUND(MOD(VALUE({NumberValue}&’’),1)*100)
May 10, 2021 11:29 AM
Welcome to the community, @Alanna_Vaughns! :grinning_face_with_big_eyes: Because the rounding operation would affect the whole number, not just the decimal portion, the rounding would need to be calculated at every position where {NumberValue}
is referenced. While you could just wrap the ROUND()
function around each instance of {NumberValue}
, I suggest making a formula field—perhaps naming it {Value Rounded}
—that is only this:
ROUND({NumberValue})
Then change all references to {NumberValue}
to point to {Value Rounded}
instead. That way the pretty-print formula operates on the rounded version all the way through.
Aug 20, 2021 09:29 AM
Hi @Justin_Barrett and @W_Vann_Hall thanks for this awesome code and example base!
I tried this technique of Rounding the number before applying the Pretty routine to it. but the routine adds the decimals back in, they are all now just .00.
EDITING to clarify my question - I guess I am looking to figure out how to do this part that is mentioned above in the overview - namely, get rid of decimals (after rounding, which I have done):
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.
Aug 20, 2021 09:46 AM
If you want to be able to have “Pretty Print” numbers without messing with editing these massively complex formulas, try out my Ready Made Formulas app. You just pick your number format and the app generates the formula for you.
You can also customize the number format if you don’t find a preset format you like:
Note that this particular formula requires a premium license, but the time you will save in setting up the formula is well worth the price.
Aug 20, 2021 11:23 AM
OK! I figured it out. If for future use, you want to kill the decimals, first round the number, and then send that number through the formula with the content deleted at the end that starts with “&.”. Basically it just kills the part of the work that adds the decimal back in, and since the incoming number is all .00, there is no loss of data. Thanks!!