This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Other questions
- Re: Converting text to number & formatting

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Solved

Jump to Solution

1
6876
0

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 19, 2021 07:20 AM

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

Solved! Go to Solution.

2 Solutions

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 19, 2021 07:57 AM

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

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 19, 2021 11:31 AM

9 Replies 9

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 19, 2021 07:57 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 19, 2021 09:37 AM

Hey Scott,

Thank you for fast reply. It worked!

Thanks for your help.

Heike

Reply

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 19, 2021 11:31 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 19, 2021 05:58 PM

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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 19, 2021 08:39 PM

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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 20, 2021 11:50 PM

@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,"-")®EX_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"))))))
```

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 21, 2021 09:27 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 22, 2021 01:54 AM

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!

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 22, 2021 09:41 AM

Reply