How to extract some specific data from within a data point using a formula?

[Hopps]

Hi. I have a set of data that has some duplicate descriptions with identical Product Codes, but the Unit data is different on some items when it comes to 'Pack Size'- see attached. 

How would I set up a formula to separate/pull out the 'Pack Size' column into the 1st number (Boxes) and the 2nd number (Units)? So that I can summarize all data in Units.....

Material ID	Material Description	Pack Size	Sum_Invoice Receipt Quantity
167115	Chocolate Celebrations Tub 650g	1 x 1 EA	128
167115	Chocolate Celebrations Tub 650g	1 x 4 EA	1092
58307	Gum Chewing Peppermint Extra Wrigleys	36 BOX x 30 EA	68

[estherb47 · Accepted solution]

Hi @Hopps 

I'd use a combination of Data Cleansing (to remove the text from the Pack Size column) and RegEx to parse the numbers into two columns, and ensure they are numbers.

Data Cleansing configuration:

RegEx configuration (save the numbers, ignore the spaces):

Let me know if that works for you.

Cheers!

Esther

[Hopps]

Perfect!! Thankyou......................had a few pesky commas in data further down, but just went back in and got the cleanser to remove punctuation as well.......bingo. 

I must learn to think further out of the box than formulas in this tool 🙂

[estherb47]

@Hopps 

So glad it worked!!!

I find that thinking in patterns helps when cleaning and parsing data, especially when Alteryx makes it so easy to work with patterns!

Cheers,

Esther