Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Add thousand separator using regex

Highlighted
Bolide

How can I add a thousand separator (comma's) to a number that's in a string field using regex?

e.g. 112982 -> 112,982 

 

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
Regex_replace([thiusands],"(\d{3})(\d{3})","$1,$2")
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
 
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
Btw, that only works for 999999 numbers.
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Bolide

slick! Yeah I added $3 to capture larger numbers.

Thanks!

 

Highlighted
Bolide

above didn't work quite right. I ended up using this solution:

 

Regex_replace([Value],"\d{1,3}(?=(\d{3})+(?!\d))","$&,") 

 

InOut
112982112,982
11298201,129,820
158547639158,547,639
5478954,789
65986,598
317317
2121

 

source: http://regexr.com/2rhmc

Alteryx Alumni (Retired)

While I love it when a nifty REGEX expression can solve the problem the easiest solution in this scenario is to use the ToString() function in the Formula tool:

add_comas.PNG

 

If you have a look at the Formula tool help file you'll see an example using the 3rd (optional) parameter to tell Alteryx you want comas. The default value is either 0 or blank.

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

ToString([Number],[Decimal Places],[Do I Want Commas])

 

Wow!  That's nifty!

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Bolide

Hi Garth

 

Of course, I should have known Alteryx has another, easier solution -  and it's in one step which is always a plus :)

Thanks everyone for their input!

Highlighted
Alteryx Alumni (Retired)

I've been working with Alteryx since 2011, and working for Alteryx since 2013. I didn't realize there was an 'add comma' option in the ToString() function 'til last year. :smileyembarrassed:

 

[sorry for the typo]

Labels