Forum Discussion
If Text.Contains transform into multi column delimiter
Hello,
I have cells withh a lot of data and I need to extract the meaningful bits into buckets...example best shows (the rest is junk.
| Typ: RESPA;Org: 80;MD: MAR/VAS - N;Qty: 6;Bin: STD4523E3.PROD.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - N;Qty: 5;Bin: STD4621E1.PROD.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - N;Qty: 2;Bin: STD5120A3.PROD.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - N;Qty: 5;Bin: STD5206E1.PROD.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - N;Qty: 4;Bin: STD5220B2.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - N;Qty: 4;Bin: STD5519D1.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 1;Bin: STD5820F3.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 6;Bin: STD6418B2.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 5;Bin: STD6519C2.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 6;Bin: STD6525A3.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 2;Bin: STD4615B1.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 6;Bin: STD4618A2.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 4;Bin: STD5601F2.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 5;Bin: STD5613E1.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 6;Bin: STD5625B3.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 6;Bin: STD5820F1.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 3;Bin: STD5820F3.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 3;Bin: STD5902F3.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 6;Bin: STD6005E3.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 1;Bin: STD6011A1.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 3;Bin: STD6401A1.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 3;Bin: STD6403B1.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 4;Bin: STD6404B3.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 3;Bin: STD6408E3.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 4;Bin: STD6622D3.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 5;Bin: STD7124E3.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 1;Bin: STD5220B2.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 2;Bin: STD6622D3.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 6;Bin: STD4308B1.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 6;Bin: STD4416B1.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 5;Bin: STD4417B1.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 6;Bin: STD5104E2.STOCK.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 5;Bin: STD5204E1.SHIPPING.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 1;Bin: STD5217C1.SHIPPING.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 6;Bin: STD5220C3.SHIPPING.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 6;Bin: STD5806E3.SHIPPING.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 6;Bin: STD5824C2.SHIPPING.PLU.._999D_ Typ: RESPA;Org: 80;MD: MAR/VAS - C;Qty: 2;Bin: STD5923B3.SHIPPING.PLU.. |
I need this to be in this form....
| MD | Qty | Bin |
| MAR/VAS - N | 18 | PROD |
| MAR/VAS - N | 8 | STOCK |
| MAR/VAS - C | 20 | SHIPPING |
| MAR/VAS - C | 108 | STOCK |
Any suggestions....there's several many rows like this. Thank you!
4 Replies
ImkeF , Ashish_Mathur , Can help on this?
I cannot understand the data. Is all that lying in a single column or is that in multiple columns?
