Converting text to number & formatting

[HeikeHoreth]

Hello everyone,

I need help with my problem. I haven’t found a solution so far.

I have an airtable where we include currency rates. Those are in this format #.##### (always five digits after the decimal point)

I’m using this formula to switch the decimal point to comma to convert the number to EU standard:

SUBSTITUTE(CONCATENATE({currency rate},""),".",",")

This works fine so far, unless there is a number like this:
How it should look like: 0,02580
What it looks like: 0,0258

The formula omits the final 0

I tried using VALUE to change it to a number field to use “formatting”, but this made things worse. It turns the number into “258”.

Any idea how to fix this?

Kind regards,
Heike

[ScottWorld · Accepted solution]

Welcome to the community, @HeikeHoreth!

Yeah, unfortunately, not only does Airtable ONLY format currency for USA/Canada residents (which is already big enough of a problem with Airtable), but Airtable isn’t even smart enough to see those final 0’s at all, which makes everything significantly more complicated than it needs to be when working with numbers in Airtable.

So you’ll actually need to create a much longer & more complex conditional formula that MANUALLY adds on a final zero to the end of the number under certain circumstances.

Since your number is always going to be 5 digits long, your formula will be relatively straightforward.

You can compare the “value of the original number rounded to 4 decimal points” to the “value of the original number rounded to 5 decimal points”. If the numbers are the same, then you’ll need to manually add on a zero to the end.

Your resulting formula would look like this::

SUBSTITUTE(CONCATENATE({currency rate},""),".",",")
&
IF(ROUND(Currency,4) = ROUND(Currency,5),"0")

[HeikeHoreth]

Hey Scott,

Thank you for fast reply. It worked!

Thanks for your help.

Heike

[ScottWorld · Accepted solution]

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

[kuovonne]

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.

[kuovonne]

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.

[Justin_Barrett]

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

[kuovonne]

Justin_Barrett:

Your response inspired me. :slightly_smiling_face:

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.

Justin_Barrett:

it was based on a much older formula written by someone else

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.

Justin_Barrett:

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.

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.

[HeikeHoreth]

Hello Kuvonne and Justin,

Thank you for your input as well.

kuovonne:

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’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!

[Justin_Barrett]

kuovonne:

Was it W_Vann_Hall’s pretty print formula? That was my inspiration.

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.