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 Separator in formula field with a text

Topic Labels: Formulas
Solved
Jump to Solution
2297 4
cancel
Showing results for 
Search instead for 
Did you mean: 
jot
4 - Data Explorer
4 - Data Explorer

Hi. 

Thank you in advance, I am wondering if there is a way to show numbers with thousand/comma separator if you combine it with a text in a formula field using concatenate. My Amount field is in currency format. 

Current Formula: 

CONCATENATE(Amount, " ", {asking-currency})

Much appreciated.  

jot_0-1697117107873.png

 

1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

Hi @jot ,

I've developed an inexplicable formula.

 

REGEX_REPLACE(
  REGEX_REPLACE(
    REGEX_REPLACE(
      REGEX_REPLACE(
        REGEX_REPLACE(
          REGEX_REPLACE(ROUND({Amount})&"", "(\\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,"
  ),
  "^,",
  ""
) & " " & {asking-currency}

 

 

See Solution in Thread

4 Replies 4
Sho
11 - Venus
11 - Venus

Hi @jot ,

I've developed an inexplicable formula.

 

REGEX_REPLACE(
  REGEX_REPLACE(
    REGEX_REPLACE(
      REGEX_REPLACE(
        REGEX_REPLACE(
          REGEX_REPLACE(ROUND({Amount})&"", "(\\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,"
  ),
  "^,",
  ""
) & " " & {asking-currency}

 

 

jot
4 - Data Explorer
4 - Data Explorer

Thank you very much! 

AcDemas
5 - Automation Enthusiast
5 - Automation Enthusiast

Is there anyway to have this formula work with decimals? 
I am working on a project where this formula would be incredibly helpful, but I need to be able populate up to 2 decimal places. 

Sho
11 - Venus
11 - Venus

How about this.

IF({Amount},
  REGEX_REPLACE(
    REGEX_REPLACE(
      REGEX_REPLACE(
        REGEX_REPLACE(
          REGEX_REPLACE(
            REGEX_REPLACE(
              ROUNDDOWN({Amount},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,"
    ),
    "^,", ""
  )&
  "." & LEFT((ROUND({Amount},2)-ROUNDDOWN({Amount},0))*100 & "0", 2)
 & " " & {asking-currency}
)