In the example I posted, the columns were populated manually for the purpose of illustrating the desired output.
When I actually do this in excel with hundreds of rows, I would go through a lengthy process to develop a solution the first time. I create a series of helper columns with string functions and if statements to break up the data in a logical place to capture the fields. I also would put certain known items in a separate table and use Vlookup to make the maintenance easier.
Essentially I'd sort the description so like items were grouped together, and strip off logical pieces. Then I'd evaluate what was left and repeat the process.
For example,
If the first word is "tlP or "Auto_Btrends" I know from the data this belongs in the Order Source field. If it is not either of those two words, I know it is a default order type.
I'd then copy the description to a temp description 1 field, excluding the first word, and re sort.
In this second sort, the first word would either be bot (meaning bought) or sold That would provide the data for the Side column. The Side data would be stripped off and the remaining phrase placed in a temp description 2 field.
That would leave the quantity. I know that is valid because it is an integer with a + in front of it for bought and a - for sold. This would provide the data for the quantity column. Clone the remaining description to a temp description 3 field, re-sort and continue.
I might then strip off data from the right side. The exchanges could be ISE, PHLX, or others, and this would give me the data for the exchange field.
Then I'd capture the price after the @ sign.
Eventually I would get all of the pieces in the right columns, but it is not easy.
I recently found a cleaner data source so I am working on a different approach to the problem.
Thanks
John
