Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Number separators formula

Topic Labels: Community Data Formulas
Solved
Jump to Solution
1422 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))
)))