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

[BenMoss · Accepted solution]

 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,

R

[BenMoss · Accepted solution]

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.

[rwicker]

@BenMoss Thank you!!