Re: Number separators formula

AcDemas · ‎Mar 27, 2024

Looking for some help regarding a formula.

Here's a little context of what I am trying to do. I am working on importing my teams marketing budget plans into Airtable, and so far everything is working well. As a final step, and in order to avoid have two column with repeated numbers for Actual Variance to Budge and Planned Variance to Budget, I wanted to create a formula combining the two numbers and then group by that column (and hide it), so that it is always present but it doesn't need to be repeated. The look that I am going for is ($ x,xxx,xxx.xx | $ x,xxx,xxx.xx)

I ran into the same issue that I found a lot of people here have faced. Once I use the currency values in a text formula, I automatically lose the "$" symbol and the number separators. The symbol (even with my limited knowledge) is easy to put back, but the separators were not.

I found this solution in the forums. I can't take any credit for it because I simply do not understand it, but it seems to work for a rounded number:

"$"&

REGEX_REPLACE(

REGEX_REPLACE(ROUND({Actual Variance to Budget Formula})&"", "(\\d{3})$", ",$1"),

"(\\d*)(\\d{3}),",

"$1,$2,"

),

"(\\d{1,3})(\\d{3}),",

",$1,$2,"

),

"(\\d{1,3}),(\\d{3}),",

"$1,$2,"

),

"^(\\d{1,3})(\\d{3}),",

"$1,$2,"

),

"^,",

""

)

The issue here is that I need it to display 2 decimals, but as soon as I input ROUND({Actual Variance to Budget Formula} , 2 )&"" in the formula, the decimals populate but the separators disappear.

Does anyone have any solutions on how to use the above formula but have the numbers display 2 decimal points?

Sho · ‎Mar 27, 2024

Adding this formula would add two decimal places.

"."&LEFT((ROUND({Value},2)-ROUNDDOWN({Value},0))*100&"0", 2)

See Solution in Thread

Sho · ‎Mar 27, 2024

Adding this formula would add two decimal places.

"."&LEFT((ROUND({Value},2)-ROUNDDOWN({Value},0))*100&"0", 2)

AcDemas · ‎Mar 28, 2024

Thank you so much for replying here and in the other post.

I added some small changes to the formula to make it work for negative numbers as well (or in this case negative = over budget) but this is working like a charm.

IF(ROUNDDOWN({Actual Variance to Budget Formula},0)>0,

"$"&IF({Actual Variance to Budget Formula},

REGEX_REPLACE(

ROUNDDOWN({Actual Variance to Budget Formula},0)&"", "(\\d{3})$", ",$1"),

"(\\d*)(\\d{3}),", "$1,$2,"

),

"(\\d{1,3})(\\d{3}),", ",$1,$2,"

),

"(\\d{1,3}),(\\d{3}),", "$1,$2,"

),

"^(\\d{1,3})(\\d{3}),", "$1,$2,"

),

"^,", ""

)&

"." & IF(ROUND({Actual Variance to Budget Formula},2)>0,LEFT((ROUND({Actual Variance to Budget Formula},2)-ROUNDDOWN({Actual Variance to Budget Formula},0))*100 & "0", 2),ABS(LEFT((ROUND({Actual Variance to Budget Formula},2)-ROUNDDOWN({Actual Variance to Budget Formula},0))*100 & "0", 3))

)),"-$"&IF({Actual Variance to Budget Formula},

REGEX_REPLACE(

ABS(ROUNDDOWN({Actual Variance to Budget Formula},0))&"", "(\\d{3})$", ",$1"),

"(\\d*)(\\d{3}),", "$1,$2,"

),

"(\\d{1,3})(\\d{3}),", ",$1,$2,"

),

"(\\d{1,3}),(\\d{3}),", "$1,$2,"

),

"^(\\d{1,3})(\\d{3}),", "$1,$2,"

),

"^,", ""

)&

"." & IF(ROUND({Actual Variance to Budget Formula},2)>0,LEFT((ROUND({Actual Variance to Budget Formula},2)-ROUNDDOWN({Actual Variance to Budget Formula},0))*100 & "0", 2),ABS(LEFT((ROUND({Actual Variance to Budget Formula},2)-ROUNDDOWN({Actual Variance to Budget Formula},0))*100 & "0", 3))

)))