Help

Number separators formula

Topic Labels: Community Data Formulas
Solved
Jump to Solution
1180 2
cancel
Showing results for 
Search instead for 
Did you mean: 
AcDemas
5 - Automation Enthusiast
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
Sho
11 - Venus
11 - Venus

Adding this formula would add two decimal places.

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

See Solution in Thread

2 Replies 2
Sho
11 - Venus
11 - Venus

Adding this formula would add two decimal places.

"."&LEFT((ROUND({Value},2)-ROUNDDOWN({Value},0))*100&"0", 2)
AcDemas
5 - Automation Enthusiast
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))
)))