Skip to main content

In theory I can create a REGEX_REPLACE that turns and integer into a string with thousands separator.   I wish AirTable had a FORMAT function but it doesn't, hence the painful work around.

In theory this should work

REGEX_REPLACE({the number}&"","[?<=\\d][?=[?:\\d(3)]+[?!\\d]  ",",")
 
this is based on a match string "(?<=\d)(?=(?:\d{3})+(?!\d))". ( not original I claim no credit, regex match strings give me a nose bleed)
 
I have learnt the hard way that Airtable uses "[" instead of "(" and you need to escape the
"\\"s - so \\d not \d
 
Also I have to turn the {the number} field into a string use the &"" trick

The formula doesn't give an error but it doesn't give me the result I want. I just get the number back If I have a number 123456789
I want to return string 123,456,789
 
I suspect the problem is the "{3}" which has brackets like a field. But what do I know.
 
Any suggestions?
 
And the reason I need a string with the thousands separator is so I can put it in an automation email.
The currency field type trick does work.
 

If you can live without the REGEX (which gives me headaches), I suggest using W Vann Hall’s Pretty-Print formula

It’s an absolute lifesaver for this sort of situation, and every time I use it I am so grateful that he posted it here. 

If you can live without the REGEX (which gives me headaches), I suggest using W Vann Hall’s Pretty-Print formula

It’s an absolute lifesaver for this sort of situation, and every time I use it I am so grateful that he posted it here. 


Thanks much appreciated.   In the end I implemented a solution like W Vann Halls approach.  It's a working solution but doesn't solve the REGEX question so I have not accepted your response as a solution.   I would still really like to understand just how REGEX is implemented in Airtable.

After formatting the number I discovered that formula fields do not display in an automation email????   By luck  and perseverance l discovered  that if you wrap the formula field tag in a HTML tag eg <b></b> then it displays. Go figure.  I have discovered the hard way that doing anything beyond very simple emails in airtable is fraught, takes a lot of trial and error and doesn't work the way AirTable's documentation suggests.

Reply


Terms & Conditions