Number Separator in formula field with a text

Topic Labels: Formulas
Solved
1530 4
cancel
Showing results for
Did you mean:
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:

Much appreciated.

1 Solution

Accepted Solutions
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}``````

4 Replies 4
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}``````

4 - Data Explorer

Thank you very much!

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.

11 - Venus

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