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

[ToniMahonie]

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

[JoshKushner]

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

[NicoleJohnson · Accepted solution]

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!

NJ

[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

[jrgo]

@ToniMahonie

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

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

Kind regards,

Jimmy

[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

BR

Toni 

[Fz]

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

[solmazhuseyin]

Hi,

a lot of time has passed after this problem but can you find any solution for this topic?