Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Convert string with commas and decimals to number

jenner85
8 - Asteroid

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

7 REPLIES 7
MarqueeCrew
20 - Arcturus
20 - Arcturus

@jenner85 ,

 

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

 

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

 

 cheers,

 

 mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
jenner85
8 - Asteroid

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
20 - Arcturus
20 - Arcturus

@jenner85 

 

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

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

else

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

Endif

 

 cheers,

 

 mark

 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
StefanvanWyk
7 - Meteor

Good and easy solution. Thank you.

Susan_Riggins
5 - Atom

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
8 - Asteroid

A Multi-Field Formula would be your next step.

SoccerTil
8 - Asteroid

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.

Labels