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!!
Aug 20, 2021 11:24 AM
Thanks @kuovonne I will check it out! As state government agency, we often have to do the hard way over the pay way!
Sep 08, 2021 12:35 AM
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: )
Oct 29, 2021 07:05 PM
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??
Oct 29, 2021 07:28 PM
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.
Nov 01, 2021 11:22 AM
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.
May 08, 2023 01:53 AM
Thanks v much @W_Vann_Hall for all the work you did on this!
May 10, 2023 04:04 PM
Wow what a legend.
Thanks for your help !
Jun 02, 2023 12:49 AM
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?
Jun 02, 2023 01:51 AM
Just to clarify, I'm looking for a formula that will use the currency field and switch the thousand and decimal separators so I can use that new field in my invoice.