community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

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

Highlighted
Meteoroid

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

Bolide

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

 

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

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):

 

2017-11-04_16-25-39.jpg

 

Sample attached showing it in action

 

 

Alteryx Certified Partner

@ToniMahonie

 

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

 

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

image.png

 

Kind regards,

 

Jimmy

Meteoroid

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 

Asteroid

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 columnafter formuladesired resultOK or KO
18.45184518.45KO
4.814,864814.864814.86OK
-1.200,86-1200.86-1200.86OK
967.4597645976.45KO
    
Labels