Skip to main content
Solved

Number Separator in formula field with a text

  • October 12, 2023
  • 5 replies
  • 288 views
J
Forum|alt.badge.img+3
  • jot
  • New Participant

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.  

 

Best answer by Sho

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}

 

 

S

5 replies

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • Answer
  • October 13, 2023

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}

 

 

A
M
J
J
Forum|alt.badge.img+3
  • jot
  • Author
  • New Participant
  • October 18, 2023

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}

 

 


Thank you very much! 

A
Forum|alt.badge.img+4
  • AcDemas
  • New Participant
  • March 27, 2024

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}

 

 


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. 

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • March 28, 2024

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

 

Ctelle
Forum|alt.badge.img+2
  • Ctelle
  • Participating Frequently
  • October 23, 2025

Edit to original inquiry below; with some very close inspection of your code I was able to figure this out to apply to mine! 😀

IF({V2 Total Impact}*125*{Credit Hours} = 0, "See request details", "$" & REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE(ROUND({V2 Total Impact}*125*{Credit Hours}, 2)&"", "(\\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," ), "^,", "" ))
-------------------------------------------------------

I have a formula using an IF statement as follows… it returns a text string if the value is 0 but I’d like it to maintain the thousands separator if the value is indeed a number. Can I apply something like this to that?

IF({V2 Total Impact}*125*{Credit Hours} = 0, "See request details", "$" & ROUND({V2 Total Impact}*125*{Credit Hours}, 2))

Terms & ConditionsAccessibility statement