Thousands separator is a dot; How to replace it with comma

Hi community,

I have a data field [Field1] formated as double and it contains entries in European/German digit format such as 4.000 (four thousend: 4000) and 32.000,89 (thirty-two thousend dot eighty-nine: 32000.98).

How can I get rid of the dot as thousands separator?

I have tried ToString([Field1], 2) in order to replace the dot in a next sept, but it turns 4.000 into 4.00, which means 4000 into 4.

Any idea how to solve this?

Thanks

Preparation

Accepted answers

NicoleJohnson

If you have numbers that contain both . period as thousands separator and , comma as decimal indicator, you'll likely need to handle a bit differently.

Try the following formula, followed by a select field to change your data type to Double.

ReplaceChar(ReplaceChar([Field1],".",""),",",".")

This will first remove the period as the thousands separator, and then change any commas as decimal indicators to periods.

Note that Alteryx does not format numbers with thousand separators while using numeric data types in your workflow, so if you are doing anything in Alteryx with these values after their conversion, you'll need to leave a thousands separator out of the result, i.e. (4000 and 32000.89 rather than 4,000 and 32,000.89).

Hope that helps!

All comments

JoshKushner

You can use the formula: Replace([Field1], ".", ",") to convert the '.' to a ','

NicoleJohnson

If you have numbers that contain both . period as thousands separator and , comma as decimal indicator, you'll likely need to handle a bit differently.

Try the following formula, followed by a select field to change your data type to Double.

ReplaceChar(ReplaceChar([Field1],".",""),",",".")

This will first remove the period as the thousands separator, and then change any commas as decimal indicators to periods.

Hope that helps!

jdunkerley79

If you can use third-party tools, I put together some tools as part of OmniBus tools which parse strings with culture into numbers (or dates as well):

Sample attached showing it in action

Omnibus Parse.yxmd

jrgo

@ToniMahonie

The formula below should do the trick. Workflow attached as well.

ToNumber(
	ReplaceChar(
		ReplaceChar([Field1],'.','')
	,',','.')
,0,",")

Kind regards,

Jimmy

ToniMahonie.yxmd

ToniMahonie

Thanks to you all. It works fine now.

I also changed with a Select tool the format from double to V_WString before applying the Replace formular.

Aurora

Thanks for the link to the OmniBus

Toni

Hey,

Please i Have a similar problem. But little bit different. I give an example in the table of what i have , and what i return if i applied your formula and what i want to return.

any help please .

Thank you

My column	after formula	desired result	OK or KO
18.45	1845	18.45	KO
4.814,86	4814.86	4814.86	OK
-1.200,86	-1200.86	-1200.86	OK
967.45	97645	976.45	KO

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703