Formatting numbers using regular expressions

[Justin_Barrett]

Every so often someone asks how to format numbers in a string the same way that they appear in a number or currency field. Various formulas have been created in the past (including some that I’ve posted in threads on this topic), but a recent thread prompted me to take another stab at the concept, and I came up with the most condensed formula that I know of for formatting whole numbers.

The formula below will currently handle numbers—positive or negative—up to 21 digits long (<= 999 quintillion), though with a single change it could support higher values if needed.

IF(Number < 0, "-") &
REGEX_REPLACE(
    ABS(Number) & "",
    REPT("(\\d{3})", (ROUND((LEN(ABS(Number) & "") + 1) / 3)) - 1) & "$",
    LEFT(",$1,$2,$3,$4,$5,$6", 3 * ((ROUND((LEN(ABS(Number) & "") + 1) / 3)) - 1))
)

If you want this displayed as currency, change the first line of the above formula to this:

IF(Number < 0, "-") & "$" &

To change the separators to periods for some other regions, replace the commas in the string that’s passed to the LEFT() function in the next-to-last line of the formula; e.g.:

    LEFT(".$1.$2.$3.$4.$5.$6", ...

To support even larger numbers, extend that string using the same pattern. Each additional [separator]$[number] combo will add support for three more digits.

    LEFT(",$1,$2,$3,$4,$5,$6,$7,$8", ...

I may revisit this later on to support numbers with decimals, but it’s tough to know how far down the road that might be.

[kuovonne]

@Justin_Barrett If you ever decide to purchase a license to Ready Made Formulas you will see that I also use regular expressions in my formula for formatting numbers as strings. (On the other hand, if you want to puzzle this one out on your own, you may not want to look at my formula ;-).

I found that the upper limit on number size for my formula was due to Airtable returning very large numbers in scientific notation. I don’t remember the exact number of decimal places where scientific notation kicked in, but I included some logic to deal with that without having to know the exact number in case Airtable changes it. (I also have an option to exclude that logic for people who know that there numbers will never get that big and want a slightly simpler formula.)

[Ambroise_Dhenai]

Thank you, the Euro version would be :slightly_smiling_face:

IF(TODO_REPLACE_WITH_YOUR_COLUMN < 0, "-") &
REGEX_REPLACE(
    ABS(TODO_REPLACE_WITH_YOUR_COLUMN) & "",
    REPT("(\\d{3})", (ROUND((LEN(ABS(TODO_REPLACE_WITH_YOUR_COLUMN) & "") + 1) / 3)) - 1) & "$",
    LEFT(" $1 $2 $3 $4 $5 $6", 3 * ((ROUND((LEN(ABS(TODO_REPLACE_WITH_YOUR_COLUMN) & "") + 1) / 3)) - 1))
)
& '€'

I’ve added this thread as resource for https://github.com/UnlyEd/airtable-utils :slightly_smiling_face:

[Dominik_Bosnjak]

Spending way too much time on something no one will ever notice or likely even see?

Lovely, here’s the premise of another formatter following a similar train of thought, just a bit less refined:

SWITCH(
	LEN(  
			  ""   & yourFieldName), 
		1, "   " & yourFieldName,
		2, "  "  & yourFieldName, 
		3,         yourFieldName)

I tend to use it to go from here

to here

whenever dealing with sub-mille data that varies a lot, triggering my OCD.

[Casey_Reid]

Any chance of seeing an update to this that includes decimal support? It would be very helpful.

[dd-adobe]

Super helpful, thank you.

How about a version that supports rounding and abbreviation like 10K, 10M, 3.4B, etc.? 🙏