Currency format in formula field

I’m guessing you can’t because it seems that \n is handled as a special case.

When I enter this as a formula:

It shows this as a result (notice the \n is missing):

Returning to the formula editor you can see that the slash has been escaped out with \\ for all values except for \n:

This strongly suggests that \n is treated differently and is either removed or replaced with a space preventing the results you’re trying to achieve.

(I’d love to be wrong on this one but it’s not looking good!) :slightly_smiling_face:

Thanks for your reply Gwynn! :slightly_smiling_face:

The addition of the line break happens in a different field in the next step, in a different formula. So it’s not applicable to the problem with the ARRAYJOIN formula not showing the currency format. It could become a problem then, but I can’t tell yet since the currency format already is removed by the ARRAYJOIN formula.

I’m wondering if there maybe is a way to change/add to the ARRAYJOIN formula (or add another formula field) in order to make a pretty view that turns the numbers back into a currency format?

Or would there be a different way to create the line breaks, without the ARRAYJOIN and SUBSTITUTE formulas, keeping the currency format from the lookup field?

I can’t figure it out.

As soon as a currency is converted to a string it loses its formatting. You can verify this by converting an amount to a string using this formula:

"" & {Amount}

I’ve added another field with the above formula and you can see the formatting is stripped off when converting to a string:

You can manually add the formatting back using this formula:

"$" & IF(FIND(".", {AmountString}) = 0, {AmountString} & ".00", {AmountString} & REPT("0", 2 - (LEN("" & {AmountString}) - FIND(".", "" & {AmountString}))))

That results in the rightmost column:

You can now go to your other table and define the ARRAYJOIN function on the manually formatted string:

ARRAYJOIN({AmountStringFormatted}, ";")

And here’s the result in the rightmost column:

Perhaps the above gets you a little closer to what you want to do.

I did try changing the semi-colon to the \n but Airtable replaces the newline with a space:

ARRAYJOIN({AmountStringFormatted}, "\n")

Thanks so much for this Gwynn! This does take me in the right direction!

I’m not quite there yet though, because in my table the output is different. I get an error for amounts that end on .00 or on two numbers after the decimal separator. And for amounts that end on one number and a 0 after the decimal separator it substitutes the final 0 for .00

I’m quite new to Airtable and formulas, so I don’t understand the formula well enough yet to understand why in my case the output is different than in your example. Maybe it’s because the {Incl Tax} field in my case is already a formula?

I got one step further and managed to get the amounts correctly when they finish on one zero. But the other ones (ending on two zeros or no zeros) still give an error.

This is what the formula looks like now:

"$" & IF(FIND(".", {Incl Tax}) = 0, {Incl Tax} & "0", {Incl Tax} & REPT("0", 2 - (LEN("" & {Incl Tax}) - FIND(".", "" & {Incl Tax}))))

This is the result in Airtable:

I really hope there’s a way to get this right. It would help me out a lot! :slightly_smiling_face:

I think I have solved it!

The problem was that the {Incl Tax} field was a formula field, which caused an error in the FIND formula in the next step.

My solution was to create a field called {Incl Tax concatenated} and then concatenate the {Incl Tax} field using the following formula:

“$” &{Incl Tax}

Then I’ve created a third field called {Incl Tax string} with the original formula from Gwynn :

IF(FIND(".", {Incl Tax concatenated}) = 0, {Incl Tax concatenated} & “.00”, {Incl Tax concatenated} & REPT(“0”, 2 - (LEN("" & {Incl Tax concatenated}) - FIND(".", “” & {Incl Tax concatenated}))))

Now it works:

So far it seems to be working properly, but I’m going to test it a bit more to make sure it’s working like it should.

Thanks so so much Gwynn for creating that formula and pointing me in the right direction! :grinning:

PS I’m not sure how to properly quote the formulas, so my apologies for the curly quotes.

There are several ways. My preference is to surround the formula in pairs of lines containing triplets of the “grave” character: ` With that in place, this in the editor:

```
IF({Field Name}, “True”, “False”)
```

…becomes this in the published post:

IF({Field Name}, "True", "False")

IF({Field Name}, "True", "False")

Thanks Justin, good to know! :slightly_smiling_face: