Dividing numbers in a lookup field separated by a ...

[AcDemas]

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? 

[Sho · Accepted solution]

Adding this formula would add two decimal places.

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

[AcDemas]

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

)))