Convert Currency (dollars & cents) to plain text so shows cents .00

[RnJ]

Hi,

How do I get currency (ie. $22.00) to display the cents “.00” when exporting? I have an integromat automation that links Airtable to email and when I use mapping for my dollar amount it only shows the whole number (ie. $22.00 shows 22 – $22.50 shows 22.5).

Thanks!

[RnJ]

I know this is probably easy, but I just can’t figure it out. I have been searching and searching, but no luck :frowning: Any help would be greatly appreciated. I just need my dollars to show cents (ie. “.00” in plain text even when the last numbers are zero. Thank you.

[RnJ]

I figured it out finally.

For those who need in future:

IF(NOT(FIND(’.’,{REFERENCED NUMBER}&’’)),{REFERENCED NUMBER}&’.00’,
IF(FIND(’.’,RIGHT({REFERENCED NUMBER}&’’,2)),{REFERENCED NUMBER}&""&‘0’,{REFERENCED NUMBER}&""))

[kuovonne · Accepted solution]

For future reference, @W_Vann_Hall has demonstration base for formatting numbers as currency to deal with these types of issues.

Number and Currency 'Pretty-Print' Routines Show and tell

tl;dr version: A demonstration base offering three approaches to formatting numbers or numerals while providing ‘pretty-print’ formatting (e.g., currency indicators, thousands and decimal separators, and so on). Allows for non-US separators and decimals, as well. The third table supports parentheses for negative values, right-aligned output with a specified number of columns, and more. A while back, in response to this query, I started on what I thought would be no more than a half-hour’s task…

[RnJ]

Thank you. I used formula in his base instead :slightly_smiling_face:

[Frazer_McLeod]

Nice! 

I was looking for this for ages and couldn't find it anywhere. I wanted a slight variation where "If the cents = .00, truncate it, else, leave the cents in. 

e.g. $32.00 --> $32 | $32.10 --> $32.10

For anyone wanting this solution in the future, here's the slightly adjusted code snippet:

IF(NOT(FIND('.',{COLUMN_NAME}&"")),{COLUMN_NAME}&'',
IF(FIND('.',RIGHT({COLUMN_NAME}&"",2)),{COLUMN_NAME}&""&'0',{COLUMN_NAME}&""))

Cheers 😎

[ScottWorld]

Also, if you use Make (formerly known as Integromat) for your automations — as the original author above said that he was using — you can use Make’s formatNumber function to format the numbers correctly.

[Lu]

@Frazer_McLeodRegular expressions are simpler, e.g.:

REGEX_REPLACE({Amount pretty}, '\.00$', '')