Converting text to number & formatting

Hey Scott,

Thank you for fast reply. It worked!

Thanks for your help.

Heike

You’re welcome! Glad I could help! :slightly_smiling_face:

I think you need a more robust formula. What if your value needs two or three trailing zeros? Since you want five decimal places, you need a formula that is prepared to tack on up to five decimal places.

I have a formula that converts a number to a string with up to four decimal places. I can adapt it to swap out the decimal symbol and add a fifth decimal place. But I cannot do it until I get to a computer.

The formula is long, but it is fairly robust.

• if the number field is blank, the formula result will be blank
• it can handle up to 15 digits to the left of the decimal
• if the number is too big, the formula will show an error
• it will always have five digits after the decimal
• it can handle both positive and negative numbers

Here is a minimized version of the formula. It expects a number field named Number.

IF({Number}&"",IF(ABS(ROUND({Number},5))>1000000000000000,ERROR(),IF(ROUND(
{Number},5)<0,"-")&IF(ABS(ROUND({Number},5))<1000,REGEX_REPLACE(INT(ABS(ROUND(
{Number},5)))&"","^(\\d+)","$1"),IF(ABS(ROUND({Number},5))<1000000,REGEX_REPLACE(
INT(ABS(ROUND({Number},5)))&"","^(\\d{1,3})(\\d{3})","$1.$2"),IF(ABS(ROUND({Number}
,5))<1000000000,REGEX_REPLACE(INT(ABS(ROUND({Number},5)))&"","^(\\d{1,3})(\\d{3})(\\d{3})",
"$1.$2.$3"),IF(ABS(ROUND({Number},5))<1000000000000,REGEX_REPLACE(INT(ABS(ROUND(
{Number},5)))&"","^(\\d{1,3})(\\d{3})(\\d{3})(\\d{3})","$1.$2.$3.$4"),IF(ABS(
ROUND({Number},5))<1000000000000000,REGEX_REPLACE(INT(ABS(ROUND({Number},5)))&"",
"^(\\d{1,3})(\\d{3})(\\d{3})(\\d{3})(\\d{3})","$1.$2.$3.$4.$5"))))))&","&IF(ROUND(
{Number},5)=ROUND({Number}),"00000",REGEX_REPLACE(ROUND({Number},5)&"","([-]?)(\\d+)(\\.)(\\d+)","$4")&
REPT("0",5-LEN(REGEX_REPLACE( ROUND({Number},5)&"","([-]?)(\\d+)(\\.)(\\d+)","$4")))))
)

Because this is a very complex formula, edit at your own risk. If your field has a different name, I recommend that you change your field name to Number, then create the formula field and paste in this formula, then change your number field back to its original name. This way you will not need to edit the formula.

When copying the formula, make sure you get the entire formula, including the closing parenthesis on the last line. I placed it on the last line by itself to make it easier to select the entire formula.

If you are interested in …

• a variation of this formula (currency symbols, percents, regular commas & decimal points, etc)
• how this formula works
• the un-minified version of this formula
• how I go about crafting formulas like this

… you can book an appointment with me.

IF({Number}&"",IF(ABS(ROUND({Number},5))>1000000000000000,ERROR(),IF(ROUND(
{Number},5)<0,"-")&IF(ABS(ROUND({Number},5))<1000,REGEX_REPLACE(INT(ABS(ROUND(
{Number},5)))&"","^(\\d+)","$1"),IF(ABS(ROUND({Number},5))<1000000,REGEX_REPLACE(
INT(ABS(ROUND({Number},5)))&"","^(\\d{1,3})(\\d{3})","$1.$2"),IF(ABS(ROUND({Number}
,5))<1000000000,REGEX_REPLACE(INT(ABS(ROUND({Number},5)))&"","^(\\d{1,3})(\\d{3})(\\d{3})",
"$1.$2.$3"),IF(ABS(ROUND({Number},5))<1000000000000,REGEX_REPLACE(INT(ABS(ROUND(
{Number},5)))&"","^(\\d{1,3})(\\d{3})(\\d{3})(\\d{3})","$1.$2.$3.$4"),IF(ABS(
ROUND({Number},5))<1000000000000000,REGEX_REPLACE(INT(ABS(ROUND({Number},5)))&"",
"^(\\d{1,3})(\\d{3})(\\d{3})(\\d{3})(\\d{3})","$1.$2.$3.$4.$5"))))))&","&IF(ROUND(
{Number},5)=ROUND({Number}),"00000",REGEX_REPLACE(ROUND({Number},5)&"","([-]?)(\\d+)(\\.)(\\d+)","$4")&
REPT("0",5-LEN(REGEX_REPLACE( ROUND({Number},5)&"","([-]?)(\\d+)(\\.)(\\d+)","$4")))))
)

@kuovonne Your response inspired me. :slightly_smiling_face: I recently helped someone with a similar formula for formatting currency, but it was based on a much older formula written by someone else, and my initial take at the time was to just extend the logic of that original formula. The problem: it was just a massive formula. Your use of regular expressions got me thinking, and I decided to play and see if the setup could be optimized further to work with varying number lengths without needing to check for each length specifically.

To help make the final formula simpler, I wrote an intermediate formula to calculate how many separators would be required. This became the {Separators} field:

(LEN(Rounded & "") - 1 - MOD(LEN(Rounded & "") - 1, 3)) / 3

I then reworked the formula above to dynamically build both the regular expression and its replacement string based on this separator count. Here’s what I came up with:

IF({Number}&"",IF(LEN(ABS(ROUND({Number},5)) & "")>15,ERROR(),IF(ROUND(
{Number},5)<0,"-")&REGEX_REPLACE(INT(ABS(ROUND({Number},5)))&"","^(\\d{1,3})"&REPT(
"(\\d{3})",Separators),"$1"&LEFT(".$2.$3.$4.$5",Separators * 3))&","&IF(ROUND(
{Number},5)=ROUND({Number}),"00000",REGEX_REPLACE(ROUND({Number},5)&"",
"([-]?)(\\d+)(\\.)(\\d+)","$4")&REPT("0",5-LEN(REGEX_REPLACE(ROUND({Number},5)&
"","([-]?)(\\d+)(\\.)(\\d+)","$4"))))))

(LEN(Rounded & "") - 1 - MOD(LEN(Rounded & "") - 1, 3)) / 3

IF({Number}&"",IF(LEN(ABS(ROUND({Number},5)) & "")>15,ERROR(),IF(ROUND(
{Number},5)<0,"-")&REGEX_REPLACE(INT(ABS(ROUND({Number},5)))&"","^(\\d{1,3})"&REPT(
"(\\d{3})",Separators),"$1"&LEFT(".$2.$3.$4.$5",Separators * 3))&","&IF(ROUND(
{Number},5)=ROUND({Number}),"00000",REGEX_REPLACE(ROUND({Number},5)&"",
"([-]?)(\\d+)(\\.)(\\d+)","$4")&REPT("0",5-LEN(REGEX_REPLACE(ROUND({Number},5)&
"","([-]?)(\\d+)(\\.)(\\d+)","$4"))))))

Awesome! I should probably do a write-up for the “Show & Tell” section, but I keep finding tweaks to the formula. Plus, I’m hoping that Airtable will release a NUMBER_FORMAT() function.

Lol. Was it W_Vann_Hall’s pretty print formula? That was my inspiration. I didn’t want to reverse engineer his formula, and I realized that regular expressions could make things easier. (I bet W_Vann_Hall could pull of some amazing things with the new regular expression functions.)

I also wanted a version that was easy to adapt – adjust the number of decimal places, the location and type of units, etc. My personal version of this formula is heavily commented and spread across many more lines.

Great idea! With this method, it won’t be necessary to check for a maximum value.

I’ll add this idea to my list of things to incorporate in my next version of the formula.

Hello Kuvonne and Justin,

Thank you for your input as well.

I’ve used Scotts formula and adjusted it a bit to counter the problem, when there are more than one missing zero at the end.

My solution:
(SUBSTITUTE(CONCATENATE({currency rate},""),".",",")&IF(ROUND({currency rate},4)=ROUND({currency rate},5),‘0’)&IF(ROUND({currency rate},3)=ROUND({currency rate},5),‘0’)&IF(ROUND({currency rate},2)=ROUND({currency rate},5),‘0’))

It works in my case.

Your formulas are indeed more complex and longer, but I will try them out as well.

Thank you!

I believe so. Another user had copied it and needed support for larger numbers. I managed to figure out the design and add what the user needed, but it was a chore. The regex version that I was able to come up with after seeing your post is about 1/5 of the length of that modified formula.