Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Formula to replace

Ekta
8 - Asteroid

Hi All, 

I would like to build a formula to replace the second "," with "."

176,044,32

 

Actually the number should be 176,044.32 but due to readability issue , number is extracted as 

176,044,32.

 

Please help me with this, thanks a lot in advance.

5 REPLIES 5
atcodedog05
22 - Nova
22 - Nova

Hi @Ekta 

 

Here is how you can do this. You can use this formula. This always replaces last , to .

 

 

REGEX_Replace([Input], "(.+),(.+)", "($1).($2)")

 

 

Workflow:

atcodedog05_0-1626278017811.png

 

 

Hope this helps : )

 

apathetichell
18 - Pollux

@atcodedog05's solution should be perfect for the problem you presented, but if for any reason you also have numbers where the format is already xxx,xxx.xx if could parse the wrong comma. If this is the case - try:

REGEX_Replace([field1], "(.+),(\d{2})$", "($1).($2)")

Ekta
8 - Asteroid

@atcodedog05  Can i please check what can be the formula to convert the 223.995.40 to 223,995.40?

due to readability issue "." can be read as "," and "," as "." .

if the number is read as 176,044,32 or 223.995.40, how can we convert the number to the correct format ?

 

Please help me with the formula. 

 

Thank you in advance

binay2448
11 - Bolide

I think the below formula will help......

 

REGEX_Replace(Replace([Field1], ".", ","), "(.+),(\d{2})$", "($1).($2)")

atcodedog05
22 - Nova
22 - Nova

Hi @Ekta 

 

Here is how you can do it.

Workflow:

atcodedog05_0-1629959794386.png

 

Formula:

REGEX_Replace(Replace([Field1], ".", ","),
 "(.+),(\d+)", "$1.$2")

 

The last comma or decimal will be converted to decimal. Also, it can handle n digits after the decimal point.

 

Hope this helps : )

 

Labels