Data Parse from combined feild

[yashd]

Hi everyone, I am trying to work on this data but finding it as I am slowly progressing it on parsing this data.

I have uploaded the excel sheet which shows how the data is and how I am trying to do, I have this type if data over 10k :(. Would really appreciate if someone could show me a simple way to do or guide me. I am using regex but not able to parse only particular data from the combined data.

[NicoleJohnson · Accepted solution]

Try the attached... uses a combination of RegEx + Transpose & Cross-Tab. This should work as long as the headers are the same in every row (Website, Discounts, Area Criteria, Typical Store, and Apparel - xyz Parent Company). Note that by using Cross Tab, spaces and dashes are replaced with underscores... you could use Dynamic Rename tool to convert some of those back if needed. Let us know if this helps! :)

Cheers,

NJ