-
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!
-
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
-
-
3. Re: Need REGEXP_REPLACE to add thounsands seperators
pooja.gandhi Aug 30, 2016 7:06 AM (in response to Lee Forst)1 of 1 people found this helpfulWhy 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:
-
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.
-
5. Re: Need REGEXP_REPLACE to add thounsands seperators
pooja.gandhi Aug 30, 2016 12:55 PM (in response to Lee Forst)2 of 2 people found this helpfulHere 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.
-
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.
-
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.