Seperate/Sort Data from a Field

rwicker

Hello!

I have a dataset that needs to have specific fields pulled from it (Attached).

Field 15 - I need in separate columns:

Article # (i.e. Article 20579)

Qty # (i.e. Qty 1000)

Amt # (i.e Amt 17.01)

***I do not need any of the other data that is in Field 15 other than what is listed above.

Then will need to Group by Article # and get Grand Totals of Qty and Amt.

Thanks!!

Rachel

Seperate Data.xlsx

Preparation

Accepted answers

BenMoss

This is a perfect usecase for Regex, well worth learning IMO.

I have used the following statement to extract the information you require.

.+Article: (\d+).+Qty: (\d+.\d+).+ Tot Amt: (\d+.\d+)

I have set the regex tool to be in 'Parse' output method.

The brackets denote a value that you wish to bring out from the string. You can then rename the fields either in the regex tool itself or after using a select tool.

You may have to make things like decimals to be optional which can be done using a | (or).

Ben

BenMoss

Sorry @rwicker, I didn't notice the nested articles within the same cell.

Could you consider splitting to create a new line every time you see ;, ;A

This could be done by using a replace formula

replace([Field],";, ;A","|A")

Then using the text to columns to split on the pipe (use the text to columns in 'split to rows' mode.

Finally you can then apply that regex statement.

All comments

BenMoss

This is a perfect usecase for Regex, well worth learning IMO.

I have used the following statement to extract the information you require.

.+Article: (\d+).+Qty: (\d+.\d+).+ Tot Amt: (\d+.\d+)

I have set the regex tool to be in 'Parse' output method.

The brackets denote a value that you wish to bring out from the string. You can then rename the fields either in the regex tool itself or after using a select tool.

You may have to make things like decimals to be optional which can be done using a | (or).

Ben

rwicker

Thank you! Yes, I would love to learn more about IMO.

rwicker

Hi Ben, @BenMoss

After reviewing the results, it is taking only the last line of the Article data set within that cell. It is not bringing all the Article data sets.

Any ideas?

Thanks,

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703