Extracting numbers on a cell

Hi All,

I need help extracting the numbers on a cell. I am doing this manually by using the text to column, but there's a lot of work to do in arranging them in a column. You can see on the Cleaning 1 tab the data I want to extract is column H arranged by Product no. (col G).

Sheet 1 is the output of using column-to-text and BOM tab is the final result I want to achieve. I am asking for help because I have 571 product no's. to do and if I do it manually I think it's not efficient.

Thank you.

Warm regards,

Albert

working data.xlsx

Transformation

Accepted answers

DataNath

Hey @Albert2107, how does something like this look? The select and filter tools just clean up the data a little to leave us with only the information that we need. Then, using RegEx in tokenize mode, we split out every individual occurrence of a number in each record of the [Article Component] field into it's own row. From there, we just apply a sort:

Splitting out Article Components.yxzp

All comments

DataNath

Splitting out Article Components.yxzp

davidskaife

Hi @Albert2107

I know you said you're doing it using the Text to Columns tool, but did you try splitting them to rows using \s as the delimiter? It appears to match what output you're looking for?

There are a lot of NULL values in it as well, hence the fairly high Record ID!

Albert2107

Thank you @DataNath, this is what I am looking for. I'll accept this as my solution.

Albert2107

Wow! Thanks for helping me @davidskaife actually I'm doing the text to column in excel, and didn't know there's a function here in alteryx. Thanks for pointing that out, this really helps me and I'm sure it will help me on my future data cleaning.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703