Allocating data from one cell to other rows and columns

New Contributor

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 filefirst 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 perfectwhat 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 columnsafter 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,



3 Replies

@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.



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


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

@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! 

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.