Convert string with commas and decimals to number

[jenner85]

Hi - what is the best way to convert these from string to numbers. I have tried and it keeps returning just the numbers before the comma. I can't remove all punctuation because I also want to keep the decimal place. thanks! 

4,006,213,560.39
19,905 386,716.32

108,471

2,108,988.82

72,053,698.07
1,453,328.96
2,364,943.17

[MarqueeCrew · Accepted solution]

@jenner85 ,

I like replace_char([field],",",'')

 you could put a list of vale's in there as ",$%#+()"

 cheers,

 mark

[jenner85]

Thanks, that worked. I also have numbers that are like this:

874.19-

How would I make that into a negative number? I am thinking an if formula, but not sure how to tell Alteryx to add - to the beginning of the number and remove it from the end. 

[MarqueeCrew]

@jenner85 

If right([field],1) = "-" Then 

- tonumber(replace_char([field],",-"))

else

tonumber(replace_char([field],","))

Endif

 cheers,

 mark

[StefanvanWyk]

Good and easy solution. Thank you.

[Susan_Riggins]

Is there any way to use this in a data cleansing tool?  In my case there are about 200 numbers coming in from Excel with commas and decimals being interpreted as strings.  Doing a replace 200 times and creating a manual cleansed version of every field is going to be incredibly tedious.  I see that you can remove punctuation but that seems to remove the decimal with the comma.  The problem is that when I convert these to Float with the Select tool, it stops reading after the comma.

[SoccerTil]

A Multi-Field Formula would be your next step.

[SoccerTil]

If you'd like a more dynamic solution, the attached workflow Transposes the columns to rows, Fomula runs on the selected columns [now rows], then puts them all back together again. You could turn this into a macro to create a conversion tool.

[YaGurlKate]

A very simple way to do this is to place a formula tool and write the function tonumber([column_name]) and then place a select tool immediately after and select one of the numeric data types like double or fixed decimal for that column. 

[sammyb84]

for me it helped to pay attention to the column that I was trying to perform a calculation against.  I kept focusing on the column that I had already changed thinking that it was still the issue cause I was still getting an error.  Pay attention to both columns metadata and be sure to change your column data type in the formula tool too 🙄