Turn on suggestions

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

Showing results for

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

- Airtable Community
- Discussions
- Ask A Question
- Other questions
- Re: Missing zero in currency data

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

8
1482
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

Jun 30, 2021 12:17 AM

I’m working on a base that makes some currency calculations and I’ve noticed that when you have an amount that ends in a 0, e.g. £10.50, the zero at the end won’t show up in markdown that I’m using in automations. It will show as £10.5 instead. For any other amount that doesn’t end in a zero, you will get the two decimal places. The same happens whether it’s a formula field, a number field or a currency field. I could probably correct this with something like IF(RIGHT, 2=".", {Number} & “0”), i.e. add a zero when the second from right character is a decimal point, but that seems like an ugly way of doing things. Any advice?

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

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

Jul 05, 2021 04:09 PM

The formatting that you see in a currency field is only for display purposes in that field. As you saw, when passing that data anywhere else, trailing zeroes are truncated. The only way currently to get them back is by using a formula. The formula that you listed isn’t formatted properly, but this will work:

```
IF(Number, "£" & Number & IF(FIND(".", Number & "") = LEN(Number & "") - 1, "0"))
```

This looks to see if the decimal point is the next-to-last character in the number, and pads a zero on the end if so.

Somewhere in the Product Suggestions category I’m pretty sure there’s a thread requesting a dedicated currency-formatting function. I encourage you to show your support there, then join us in prayer that such a function is added eventually. :slightly_smiling_face:

Reply

2 Replies 2

Solved
See Solution in Thread

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

Jul 05, 2021 04:09 PM

The formatting that you see in a currency field is only for display purposes in that field. As you saw, when passing that data anywhere else, trailing zeroes are truncated. The only way currently to get them back is by using a formula. The formula that you listed isn’t formatted properly, but this will work:

```
IF(Number, "£" & Number & IF(FIND(".", Number & "") = LEN(Number & "") - 1, "0"))
```

This looks to see if the decimal point is the next-to-last character in the number, and pads a zero on the end if so.

Somewhere in the Product Suggestions category I’m pretty sure there’s a thread requesting a dedicated currency-formatting function. I encourage you to show your support there, then join us in prayer that such a function is added eventually. :slightly_smiling_face:

Reply

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

Jul 05, 2021 05:44 PM

Formatting numbers is such a difficult task that I wrote an app that helps create a formula to format numbers (among other formulas): Ready Made Formulas. This particular formula requires a premium license, which can be purchased from Gumroad.

If you want a bit more formatting than you can easily find in a free formula, considering investing in my app.

- pick your decimal symbol and grouping symbol (swap
`.`

and`,`

) - works with large numbers, small number, negative numbers, and zero
- round to a specific number of decimal places for calculated numbers with unpredictable decimal places
- point-and-click and copy-and-paste ease of use

Reply