8 Replies Latest reply on Apr 18, 2019 8:12 AM by Humberto Higuera

Need REGEXP_REPLACE to add thounsands seperators

Lee Forst Aug 29, 2016 3:52 PM

I'm not good at regular expressions. So I'm struggling with implementing the correct syntax to add thousands separators for a whole number converted to a string.

Consider the following

REGEXP_REPLACE(str(round([My Value],0)),"<pattern>","<replacement>")

So 123 would be 123

1234 would be 1,234

12345 would be 12,345

123456 would be 123,456

1234567 would be 1,234,567

12345678 would be 12,345,678

123456789 would be 123,456,789

1234567890 would be 12,34,567,890

and so on...

Thank you!

1. Re: Need REGEXP_REPLACE to add thounsands seperators

pooja.gandhi Aug 30, 2016 6:19 AM (in response to Lee Forst)

Hi Lee!

Why are you converting the number to a string? What does a typical data point look like? Can you provide some sample data on which you are utilizing the above formula? Do you need it in STR()?

Thanks!
Like Show 0 Likes(0)

Actions
2. Re: Need REGEXP_REPLACE to add thounsands seperators

Lee Forst Aug 30, 2016 6:35 AM (in response to pooja.gandhi)
Thanks Pooja for the reply. The reason is because we need to concatenate a calculated field with some text to display in a view. When doing this, the formatting set on the calculated field is not preserved. Therefore, we need to apply the commas as needed. Attached is a totally made up example to get the point across. I know in other applications you can use Regular Express Replace functions to do this. And I've tried applying patterns that should do this, but nothing seems to work. Again, probably a lack of my knowledge of RegEx.

RegEx Example.twbx 1.2 MB
Like Show 0 Likes(0)

Actions
3. Re: Need REGEXP_REPLACE to add thounsands seperators

pooja.gandhi Aug 30, 2016 7:06 AM (in response to Lee Forst)

Why not place the calculated field on ‘text’ on marks card instead of placing it on rows? Then add the extra descriptive text you plan on adding in the text box like so:

1 of 1 people found this helpful
Like Show 1 Likes(1)

Actions
4. Re: Need REGEXP_REPLACE to add thounsands seperators

Lee Forst Aug 30, 2016 8:37 AM (in response to pooja.gandhi)

Thank you Pooja for the suggestion. However, that is not the use case we have and we need to do it the way I've described.
Like Show 0 Likes(0)

Actions
5. Re: Need REGEXP_REPLACE to add thounsands seperators

pooja.gandhi Aug 30, 2016 12:55 PM (in response to Lee Forst)

Here you go, Lee!

REGEXP_REPLACE(REGEXP_REPLACE([Display Text], '([0-9](?=(?:[0-9]{3})+(?![0-9])))', '$1,'), ':\s', ': $')

Basically looks at the display text to see the number patterns in the grouping described. Take a look at the cheatsheet on this website to see what each grouping means:

RegExr: Learn, Build, & Test RegEx

The outer REGEXP_REPLACE() is basically looking at the : and the preceding space and replacing it with : and a $ sign to denote currency. Hope this helps!

Pooja.
2 of 2 people found this helpful
Like Show 2 Likes(2)

Actions
6. Re: Need REGEXP_REPLACE to add thounsands seperators

Lee Forst Aug 30, 2016 1:04 PM (in response to pooja.gandhi)

Thank you so much. I will take the time to look at the suggested link to help me understand better. While this solves the problem at hand, there is one thing I noticed. If the value is not a whole number, but rather something like 1234.56, it produces a result like 1,234.5,599,999,999,999. Any ideas as to why this is occurring.
Like Show 0 Likes(0)

Actions
7. Re: Need REGEXP_REPLACE to add thounsands seperators

Zale Zhao Mar 15, 2018 12:48 PM (in response to pooja.gandhi)

This is awesome! Help me a lot.
Like Show 0 Likes(0)

Actions
8. Re: Need REGEXP_REPLACE to add thounsands seperators

Humberto Higuera Apr 18, 2019 8:12 AM (in response to Lee Forst)

Thanks Pooja Gandhi This is fantastic!...
Performance-wise I don't know how regular expressions compare to nested IFs and LENs and all the other solutions you can think of with strings, but this is the cleanest way to do this BY FAR!!
Like Show 0 Likes(0)

Actions