Allocating data from one cell to other rows and columns

%3CLINGO-SUB%20id%3D%22lingo-sub-2980433%22%20slang%3D%22en-US%22%3EAllocating%20data%20from%20one%20cell%20to%20other%20rows%20and%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2980433%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20created%20a%20purchase%20order%20request%20from%20Jotform%2C%20but%20when%20I%20download%20the%20Excel%20file%2C%20all%20the%20information%20from%20the%20products%20come%20in%20one%20cell.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22how%20it%20is%20looking.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F327972i51BDB9D3DB1CB3A8%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22how%20it%20is%20looking.PNG%22%20alt%3D%22first%20excel%20file%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3Efirst%20excel%20file%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20need%20the%20information%20to%20be%20allocated%20on%20separated%20columns%20and%20rows%20(example%20in%20the%20image%20below).%3C%2FP%3E%3CP%3EI've%20tried%20using%20the%20replace%20tool%20%2B%20text%20to%20columns%20but%20it%20doesn't%20work%20for%20me%2C%20it%20only%20register%20one%20paragraph%20from%20the%20text%20I%20receive.%20It%20is%20important%20to%20notice%20that%20the%20different%20products%20are%20separated%20by%20paragraphs%20(by%20pressing%20Enter).%3C%2FP%3E%3CP%3EThis%20is%20the%20ideal%20layout%20for%20me%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ideal%20scenario.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F327973iD09976F5DA4D0714%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22ideal%20scenario.PNG%22%20alt%3D%22what%20would%20be%20perfect%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3Ewhat%20would%20be%20perfect%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E(if%20we%20could%20%22merge%26amp%3Bcenter%22%20the%20empty%20cells%20would%20be%20nice%2C%20but%20not%20needed)%3C%2FP%3E%3CP%3EIf%20I'm%20able%20to%20achieve%20this%2C%20I%20can%20use%20%22Text%20to%20Columns%22%20to%20look%20like%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22final%20form.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F327974i2BC3FFCDB11B6438%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22final%20form.PNG%22%20alt%3D%22after%20text%20to%20columns%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3Eafter%20text%20to%20columns%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAnd%20this%20is%20the%20end%20result%20I%20want.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20needs%20to%20be%20done%20automatically%20for%20me%20(Macro%20and%2For%20VBA)%2C%20because%20this%20file%20keeps%20updating%20after%2015%20days.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20on%20how%20to%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%2C%3C%2FP%3E%3CP%3ECaio%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2980433%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2980766%22%20slang%3D%22en-US%22%3ERe%3A%20Allocating%20data%20from%20one%20cell%20to%20other%20rows%20and%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2980766%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1222737%22%20target%3D%22_blank%22%3E%40ccgbalarin%3C%2FA%3E%26nbsp%3BThe%20attached%20file%20contains%20a%20table%20created%20with%20Power%20Query.%20It%20resembles%20your%20ideal%20layout%2C%20noting%20that%20merging%20cells%20is%20something%20you%20should%20avoid.%20Cosmetically%20nice%20looking%20but%20a%20pain%20when%20you%20want%20to%20do%20any%20analysis%20with%20the%20data%20afterwards.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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,

Caio

 

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.

 

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

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