V_string to number from a csv file

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

Input

Common Use Cases

Best Practices

Accepted answers

CoG

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

this sets the decimal separator to a comma.

All comments

atcodedog05

Hi @tore_nielsen

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

Screenshot 2024-10-30 195718.png

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

Screenshot 2024-10-30 200430.png

Hope this helps : )

Conversion.yxmd

Conversion v2.yxmd

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

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

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335