Number formatting

[jannis2021]

Hi -
I am wondering if its possible to set up a generic multifield expression to fix common number formatting issues. Ideally it would handle several types of issues (i.e. parenthesis depicting negatives, trailing negatives, commas, etc. even if not all those issues are present. See the expression below for an example of what I am going for. Currently the results all show zero as none of my sample data have all the issues the expression is attempting to address. Any thoughts? Thanks.

Replace([_CurrentField_],",","")
AND
Replace([_CurrentField_],"(","-")
AND
Replace([_CurrentField_],")","")
AND
Regex_Replace([_CurrentField_],"(.*)\-$","-$1")

Sample #	Expected Result
8,000.00	8000.00
(8,000)	-8000.00
8,000-	-8000.00

[JoaoLeiteV · Accepted solution]

Hey there,

Instead of an "AND" Condition, have you tried using "OR"?

Edit: You could also try using all of the formulas in a single one: Regex_Replace(Replace(Replace(Replace([Sample #],",",""),"(","-"),")",""),"(.*)\-$","-$1")

[Jean-Balteryx · Accepted solution]

Hi @jannis2021 ,

The problem comes from using AND keyword. Using it, you are testing your field based on your expression and it returns à to indicate it's false.

What you need to do is chain your expressions :

Replace(Replace(Replace(Regex_Replace([_CurrentField_],"(.*)\-$","-$1"),")",""),"(","-"),",","")

[MichaelSu · Accepted solution]

Hi @jannis2021 ,

In addition to @Jean-Balteryx  and @JoaoLeiteV solutions provided above, you might also find the number cleansing macro on the Alteryx Gallery helpful. @Treyson built this a while back but it may help to automate some of the formatting you are looking to do. 

https://gallery.alteryx.com/#!app/Number-Cleansing-Macro/5b6df1870462d71090803ac9

Thanks,

Mike

[Treyson]

@MichaelSu that's a blast from the past! I had to open it back up to check. it does account for parenthesis and allows for folks that use commas instead of periods for the radix character to select that. 

Also fun thing the radix character is that division mark between full numbers and decimals. That's what I remember from making this macro. Learning that.

[MichaelSu]

@Treyson  some things are just timeless...Wonderwall...Mr. Brightside...Treyson's macro!

Definitely a good one!

[apathetichell]

@MichaelSu is @Treyson  Liam or Noel?

[Treyson]

I think Treyson1 is Treyson. Let me confirm.

[Treyson]

Confirmed, it’s this guy:

https://www.linkedin.com/in/dynamicinput

[apathetichell]

@Treysonmusic reference based upon @MichaelSu 's comment... but I think your music tastes and mine may converge and stray fairly far from Oasis...