Replace . with , and , with dot

[JDong]

Hi All,

Maybe trivial but I am not able to get the solution 🙂

I have the following data

372.742,00 $
10.241,00 $
355.295,00 $
7.206,00 $
65.480,00 $
65.480,00 $

I want to replace the . with , and , with .

Expected result is 

372,742.00 $
10,241.00 $
355,295.00 $
7,206.00 $
65,480.00 $

Any way  to achieve this result within a calculation like replace ?

Using 2 replaces updates all instances

[apathetichell · Accepted solution]

regex replace with marked groups...

regex tool..

replace mode.

formula:

(\d+)\.(\d+),(.*)

In the replace text field:

$1,$2.$3

[mpennington]

I think this should work for you:

REGEX_Replace([Data], '(.*)\.(.*),(.*)', '$1,$2.$3')

 

[JDong]

Thanks how about in the opposite scenario...how can I change the regex ?

372,742.00 $
10,241.00 $
355,295.00 $
7,206.00 $
65,480.00 $

Expected output

372.742,00 $
10.241,00 $
355.295,00 $
7.206,00 $
65.480,00 $
65.480,00 $

[apathetichell]

same regex formula exactly - just swap the position of the "\." and the ","

Note my regextool and @mpennington 's regex_replace formula version should work identically and both should be able to solve your problem. There is slightly different syntax (mine requires digits, and his will work with any characters) but both should do great for your situation.

[JDong]

Thanks and is a easy way to generate such regex for specific use cases ? 

[apathetichell]

The best way to learn regex is to use it on a regular basis - but for starters the Alteryx training videos under Learn are super useful as are some of the Weekly Challenges which recommend it...

[AdamR_AYX]

And here is a non RegEx solution

Replace(Replace(Replace([Field1],
",", "¬"),
".", ","),
"¬", ".")

Replace the , with a random character

then replace . with ,

then finally random character to .

[ChrisTX]

easy way to generate such regex....

Generally, RegEx is not "easy".  It's a skill you improve over time.  Try this website to test expressions:  regex101.com

Chris