Skip to main content

Formatting numbers using regular expressions

  • September 6, 2021
  • 7 replies
  • 51 views
Justin_Barrett
Forum|alt.badge.img+20

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.

    7 replies

    kuovonne
    Forum|alt.badge.img+27
    • kuovonne
    • Brainy
    • 6002 replies
    • September 6, 2021

    @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.)

    A
    Forum|alt.badge.img+17

    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:

    D
    Justin_Barrett
    2 people like this
    • D
      Delval_Celine
    • Justin_Barrett
      Justin_Barrett
    D
    Forum|alt.badge.img+17
    Ambroise_Dhenai wrote:

    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:


    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.

    J
    1 person likes this
    • J
      Jort_Klarenbeek
    C
    Forum|alt.badge.img+4
    • Casey_Reid
    • Participating Frequently
    • 6 replies
    • February 27, 2023

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

    J
    1 person likes this
    • J
      jouejy
    D
    Forum|alt.badge.img+2
    • dd-adobe
    • New Participant
    • 1 reply
    • March 14, 2023

    Super helpful, thank you.

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

    J
    1 person likes this
    • J
      jouejy
    M
    Forum|alt.badge.img+1
    • Mark_Poulton
    • Participating Frequently
    • 5 replies
    • May 19, 2023

    I took this example from the OP which seemed to be limited to just whole numbers and manipulated it a bit to take into account the decimal places as well.  Seems to be working.  The target is a currency field (which is obviously past due and the left column in the pic below) .  I just sort of hacked at this until I figured something out but it is much smaller than some of the others I have found.  Assumes 2 decimal places (i.e. currency).  I guess if you only want 1 decimal, you could round the whole thing with 1 decimal.  Probably would work.  Basically if you got whole numbers and 2 decimal numbers, this will format it to a currency number with 2 decimals and a $ sign plus.a space in front of it as a text string.  I use this to fill in dollar amounts in PDFs and even though Airtable shows it as formatted, the raw data being sent to the PDF Filler is just a regular number.  I thought maybe using formatting in the PDF to say that this field is a currency would resolve it, but it did not.  I am not a programer BTW.

     

     

     

    IF(SEARCH(".",CONCATENATE({Past Due Amount})),IF({Past Due Amount} < 0, "-") & "$ " & REGEX_REPLACE( ABS(LEFT(CONCATENATE({Past Due Amount}),LEN(CONCATENATE({Past Due Amount}))-3)) & "", REPT("(\\d{3})", (ROUND((LEN(ABS(LEFT(CONCATENATE({Past Due Amount}),LEN(CONCATENATE({Past Due Amount}))-3)) & "") + 1) / 3)) - 1) & "$", LEFT(",$1,$2,$3,$4,$5,$6", 3 * ((ROUND((LEN(ABS(LEFT(CONCATENATE({Past Due Amount}),LEN(CONCATENATE({Past Due Amount}))-3)) & "") + 1) / 3)) - 1)) )&MID(CONCATENATE({Past Due Amount}),LEN(CONCATENATE({Past Due Amount}))-2,LEN(CONCATENATE({Past Due Amount}))),(IF({Past Due Amount} < 0, "-") & "$ " & REGEX_REPLACE( ABS({Past Due Amount}) & "", REPT("(\\d{3})", (ROUND((LEN(ABS({Past Due Amount}) & "") + 1) / 3)) - 1) & "$", LEFT(",$1,$2,$3,$4,$5,$6", 3 * ((ROUND((LEN(ABS({Past Due Amount}) & "") + 1) / 3)) - 1)) ))&'.00')

     

     

     

    D
    Forum|alt.badge.img+3
    • dangngocbinh
    • New Participant
    • 2 replies
    • May 10, 2024

    You are amazing. This is complex formula and i try to learn it.

    Thank you so much.

    Reply


    Most helpful members this week

    Terms & Conditions