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

Datasets

Accepted answers

JoaoLeiteV

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

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

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

All comments

JoaoLeiteV

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

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

Hi @jannis2021 ,

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

Treyson

Oh man. The Oasis reference is missed on me, that’s for sure. I would have gotten Brandon Flowers though.

apathetichell

@TreysonWe've all been there - but I clearly should have looked at your instagram first...

apathetichell

In my defense a large percentage of Alteryx gurus seem to be UK based btw - should have asked what John Carpenter track it was... I call dibs on Jimmy Chambers's "You Can't Fight It"

jannis2021

Thanks all for the replies. I apparently didn't have notifications on to email me when a response was obtained, apologies for letting this one linger as an open discussion!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703