Right now, to format values pulled from a Currency field in a Formula field, users have to use something akin to the following formula to get “$65,756” to display as such. Otherwise, AirTable displays it as “65756”, no commas, no currency symbol:
This is obviously inconvenient, and it doesn’t even take into account decimals or amounts greater than $999,999,999.
I propose a new formula function that operates something like this: CURRENCY(>number or field reference], fcurrency symbol]) => CURRENCY(12345.6,"$") = $12,345.60
Page 1 / 2
If the output of your formula is going to be a number, you can use the formatting tab in the field customization menu to do this—just switch the Format from Integer to Currency.
If the output of your formula is going to be text rather than a number, then you will not be able to use the formatting tab in this way (this might be the case, if, for example, you’re concatenating the value from a currency field with a value from a text field). However, if you’re just performing mathematical operations on a value from the currency field, this shouldn’t be an issue.
That only works if there the formula presents the number by itself. It doesnt help for formulas that concatenate plain text along with whatevery currency field is being referenced.
A CURRENCY() function is still requested.
If the output of your formula is going to be a number, you can use the formatting tab in the field customization menu to do this—just switch the Format from Integer to Currency.
If the output of your formula is going to be text rather than a number, then you will not be able to use the formatting tab in this way (this might be the case, if, for example, you’re concatenating the value from a currency field with a value from a text field). However, if you’re just performing mathematical operations on a value from the currency field, this shouldn’t be an issue.
@Katherine_Duh if you’d like a use case, here’s a shot from my table that sorely needs such a function:
To keep my table a little cleaner/legible, I combined 5 Currency fields into one column using a formula in the {Budget Summary} field. However, this means the formula needs to include the 774 character snippet (which in reality would be longer to handle decimals) featured above for each number.
The CURRENCY() function could operate just like DATETIME_FORMAT() does and would only make formulas less cluttered
@Katherine_Duh if you’d like a use case, here’s a shot from my table that sorely needs such a function:
To keep my table a little cleaner/legible, I combined 5 Currency fields into one column using a formula in the {Budget Summary} field. However, this means the formula needs to include the 774 character snippet (which in reality would be longer to handle decimals) featured above for each number.
The CURRENCY() function could operate just like DATETIME_FORMAT() does and would only make formulas less cluttered
What is your Base structure? It seems to me like you could handle that just with Grouping and summary bars.
What is your Base structure? It seems to me like you could handle that just with Grouping and summary bars.
The base already has groups as summary bars implemented and they don’t do whatthat formula is doing. What the mistitled {Budget Summary} field is doing is concatenating several lookups so they just appear in one field on top of each other, simply because I like that display.
All fields represented in {Budget Summary}, which are lookups, are hidden so I can look at one field to compare a cost breakdown as opposed to 5, where some of those five are often blank.
I have a similar need, would appreciate this feature. I have the need to put together a range of dollar amounts, such as $300-$500 which the format choice for formula fields won’t accommodate.
Great point, I can think of many use cases when a user might want to show a range of prices in a cell.
If the output of your formula is going to be a number, you can use the formatting tab in the field customization menu to do this—just switch the Format from Integer to Currency.
If the output of your formula is going to be text rather than a number, then you will not be able to use the formatting tab in this way (this might be the case, if, for example, you’re concatenating the value from a currency field with a value from a text field). However, if you’re just performing mathematical operations on a value from the currency field, this shouldn’t be an issue.
I also would like to request a function that formats to currency. I have a column that the formula pulls a day of the week and an amount earned in to one entry, and because there is now text in the solution, the formatting loses the currency symbol and any zeros in the decimal value.
Just a reminder, a suite of ‘pretty-print’ routines can be found here that handle formatting of numbers or numerals with zero-padded decimal values; thousands dividers; user-specified thousands and decimal characters; left-, right-, or decimal alignment, even in grid views; use of parentheses or hyphen/hyphen-minus to indicate negative values; and more. The base includes an interactive table, as well, that allows one to enable/disable various options through checkboxes and see the effects. Several solutions of varying complexity are provided, to allow one to choose as light-weight an approach as possible.
Thanks W, but again, its incredibly inconvenient for users to have to write formulas that complex for such a simple request.
+1 for me! This function would be fantastic!
+1 for me! This function would be fantastic!
+1 this feels like a fairly sizeable omission
Would also +1 the ability to format numbers within a formula like this.
Seems to me the need would be also to reformat a number output (as text from another formula) into plain number format. e.g. IF(Column1=ABCD,“6666”,“tryagain”) will always produce text string, whether the result is 6666 or tryagain. So the result can’t be used in subsequent formulas. Still looking for answers how to solve it.
UPDATE: OK sorry, I found a solution to my “Number” format problem. Crazy I need to add another formula to do something so simple. Formula needded is … VALUE({NumberIWant}&’’)
This is not giving any answer to the question of the OP but I am leaving this here just in case somebody looking for the answer, it seems related to me.
Source:
Hi Kamille, I tried this formula but when I click ‘Formatting’ to see how it looks it says “Your field is not fully configured. You need to have a valid field configuration in the formula tab before adding formatting options”
What did I miss?
Hi Kamille, I tried this formula but when I click ‘Formatting’ to see how it looks it says “Your field is not fully configured. You need to have a valid field configuration in the formula tab before adding formatting options”
What did I miss?
@Creative_Renaissance You’ve missed nothing, you can’t format the field as anything other than a string because it returns a string.
I’d still love a CURRENCY() function, but here’s a solution in the meantime using the scripting app! This particular use-case involved formatting just one number prior to sending it in an email, but could easily be adapted.
+1 for me. I use a formula to concatenate a budget line so that the linked record makes sense when you see it in the link. Right now it looks like ‘Smith Foundation - 100000’. With this feature, it would return ‘Smith Foundation - $100,000’. Thanks!
Count me in - I too need this feature! @Kamille_Parks you’re brilliant!
As we speak, I’m working on creating a formula title field that pulls in the “Fee” from a currency field. I scrolled through the entire list of functions only to find that no such native function exists.
@Kamille_Parks is absolutely right. A “Currency()” filter should be baked into the cake. No reaching for contrived solutions. So yeah, I’m a +1 on this suggestion.
I’d still love a CURRENCY() function, but here’s a solution in the meantime using the scripting app! This particular use-case involved formatting just one number prior to sending it in an email, but could easily be adapted.
Is there a way to use/tweak the script to run it in the table itself? I need it for displaying in Softr webapp. I was hoping after running the script, the string version would be created in a given field and would keep the currency, comma and decimal.
+1 this request - I’d like this for a formula so that the field that displays in the calendar view of my budget is something like $20 - American Express
+1 this request - I’d like this for a formula so that the field that displays in the calendar view of my budget is something like $20 - American Express
My app Ready-Made Formulas will generate a formula that does this. You can pick from several different common formats, or you can choose a custom format.
For a custom format, you can enter a currency symbol, how many decimal places, and several other options.
This formula does require a premium license, but it saves a lot of time versus finding and implementing less robust formulas on the internet. Plus, one license grants access to all the formulas in the app for one base.
+1 for this!!
I get a lot of numbers that display as “13.5” instead of “13.50” or “$13.50” when in a string. Super frustrating that I can’t turn it into a currency via formula, since I can’t do it via formatting.
