Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
ALTER.NEXT:

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

SAVE YOUR SPOT
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
SOLVED

Convert string with commas and decimals to number

Highlighted
7 - Meteor

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

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@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 reboot. Order shall return.
Highlighted
7 - Meteor

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. 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@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 reboot. Order shall return.
Labels