V_string to number from a csv file

[tore_nielsen]

Hi Community

I am struggling to get V_string to numeric data. I have tried the formula ToNumber([Field]), but it removes the comma separator which is a comma. see attached picture.

The picture on the left is before the ToNumber formula

Before ToNumberAfter ToNumber on the left column

Anyone who have an idea of what to do? 

I would prefer the following when I have made the formula 1.234.567,89 and when [null] or ,0000 then 0,00

[atcodedog05]

Hi @tore_nielsen 

You can use multi-field formula tool to remove "," (comma) and later convert it. Try something like below.

For single column just use a single formula tool and later use select tool to convert to number

Hope this helps : )

[alexnajm]

@tore_nielsen that is correct, it will remove the comma separator since that is not a numeric character. if you want to display a comma character for a numeric value just for display purposes, you can hit the three zeros (000) in your results window

[CoG · Accepted solution]

You can try the following: ToNumber([Field1],1,0,",") 

this sets the decimal separator to a comma.

[tore_nielsen]

Hi @alexnajm and @atcodedog05 

Thanks for your comments

I actually don't want to remove the comma, because it's not a thousand separator. 

I want to keep the comma, and if possible get a period as a thousand separator.

And when null or ,0000 then 0,00

[atcodedog05]

Hi @tore_nielsen 

If it is using separator "," (comma) than the data cannot be used as numeric datatype

[CoG]

^The formula I included above will treat the comma as the decimal separator. Unfortunately, and for reasons unknown to me, custom separators (thousands and decimal) will not be reflected in the results pane. Unless your output data source has the proper formatting for numeric types, then you will need to use a Formula Tool at the end of the workflow (or Multi-Field Formula Tool) to convert type back to String using ToString(x, numDec, [thousandsSeparator],[decimalSeparator]).

Hope this helps and Happy Solving!

[tore_nielsen]

Hi @CoG

I almost did the job, except it was a period as decimal separator.

I just used Select tool to make it fixed decimal, and then it's all fine.

Thanks a lot