Allocating data from one cell to other rows and columns

[ccgbalarin]

Hi everyone!

I created a purchase order request from Jotform, but when I download the Excel file, all the information from the products come in one cell.

first excel file

I need the information to be allocated on separated columns and rows (example in the image below).

I've tried using the replace tool + text to columns but it doesn't work for me, it only register one paragraph from the text I receive. It is important to notice that the different products are separated by paragraphs (by pressing Enter).

This is the ideal layout for me:

what would be perfect

(if we could "merge&center" the empty cells would be nice, but not needed)

If I'm able to achieve this, I can use "Text to Columns" to look like this:

after text to columns

And this is the end result I want.

It needs to be done automatically for me (Macro and/or VBA), because this file keeps updating after 15 days.

Any ideas on how to do this?

Thanks in advance,

Caio

[#2]

@ccgbalarin The attached file contains a table created with Power Query. It resembles your ideal layout, noting that merging cells is something you should avoid. Cosmetically nice looking but a pain when you want to do any analysis with the data afterwards.

[#3]

@Riny_van_Eekelen 

Thanks Riny! It's exactly what I was looking for.

Can I do a Macro with the steps used in Power Query?

[#4]

@ccgbalarin Lear PowerQuery. It's much easier and more user friendly than VBA. The link below would be a good start. Somewhat of a learning curve, but once you get a feel for it, you wouldn't want to use anything else. And don't give up!

https://exceloffthegrid.com/power-query-introduction/ 

Read through the material and you'll learn about splitting columns into new rows. In your case the special delimiter is a "line-feed". And you'll be able to follow the few steps I applied to transform the raw data.