Convert string with commas and decimals to number

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

Regex

Tips and Tricks

Best Practices

Preparation

Accepted answers

MarqueeCrew

@jenner85 ,

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

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

cheers,

mark

All comments

MarqueeCrew

@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.

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.

dynamicMultiFormula.yxmd

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 🙄

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335