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

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

Workflow

Datasets

Tips and Tricks

Transformation

Help

Data Investigation

Accepted answers

estherb47

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

All comments

estherb47

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 🙂

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703