ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

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

RegExp: Add a "-" every 3 characters from right to left

marcusblackhill
11 - Bolide

So, close to what @mpennington  present, I think you can combine these 2 formulas:

  1. Reversestring(regex_replace(ReverseString([Field1]),"(\d{3})","$1-"))
  2. IIF(Left([Field1],1)="-", Right([Field1], Length([Field1])-1),[Field1])

 

Like that:

marcusmontenegro_2-1618516246048.png

 

marcusmontenegro_0-1618516225359.pngmarcusmontenegro_1-1618516232981.png

 

mpennington
11 - Bolide

@BautistaC888 , 

 

Definitely along the same line as @marcusblackhill , just a slightly different variation, that is harder to read, but is should handle up to 15 digit numbers:

 

 

TrimLeft(
REGEX_Replace(
ReverseString(REGEX_Replace(
ReverseString((ToString([Number],0))),
"(\d{3})(\d{3})?(\d{3})?(\d{3})?(\d{3})?(\d{0,2})?","$1-$2-$3-$4-$5-$6")
),'-{2,}','-'),'-')

 

RegExReverse2.jpg

apathetichell
13 - Pulsar

There's been a lot of great answers on this thread from @mpennington  and @marcusblackhill 

 

here's one more example of the magnificent value of regex in replace mode...

 

(\d{3}) in replace mode

$1- (replacement text)

 

and a few reverses and a trim...

 

Or trimleft(reversestring(REGEX_Replace(reversestring(tostring([Field1])), "(\d{3})", "$1-")),"-") for the one tool formula.

 

 

mpennington
11 - Bolide

@apathetichell I really like your method, as it works regardless of the length. Great stuff!

apathetichell
13 - Pulsar

Thanks! I love learning from all of these different approaches on these discussions!

RaviP
8 - Asteroid

Really like @apathetichell 's solution. It is more generic.

 

If it is specifically 3 characters, we could may be just use a thousand separator?

replace(tostring([Field],0,1),',','-')

 

Ravi

Labels