Number separators formula

Topic Labels: Community Data Formulas
Solved
557 2
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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(
REGEX_REPLACE(
REGEX_REPLACE(
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?
1 Solution

Accepted Solutions
11 - Venus

Adding this formula would add two decimal places.

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

Adding this formula would add two decimal places.

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

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(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
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(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
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))
)))