Convert Data from one cell into multiple columns? Help!

%3CLINGO-SUB%20id%3D%22lingo-sub-1760127%22%20slang%3D%22en-US%22%3EConvert%20Data%20from%20one%20cell%20into%20multiple%20columns%3F%20Help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1760127%22%20slang%3D%22en-US%22%3E%3CDIV%3E%3CDIV%20class%3D%22resolved%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%3E%3CDIV%20class%3D%22resolved%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%3E%3CDIV%20class%3D%22resolved%22%3E%3CSPAN%3EI%20have%20an%20order%20form%20online%20that%20does%20everything%20we%20want%20it%20to%20do%20except%20spit%20out%20a%20report%20that%20makes%20any%20sense%20for%20us%20to%20use%20when%20it%20comes%20to%20placing%20our%20orders.%20And%20they%20haven't%20offered%20much%20in%20terms%20of%20helpful%20solutions%2C%20so%20I%20figured%20I%20would%20try%20here.%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%2C%20the%20problem%20is%20that%20in%20the%20excel%20report%20that%20comes%20from%20the%20form%2C%20every%20person's%20order%20goes%20into%20one%20cell%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%222020-10-08.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F225163i250ED30B26A64A8F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%222020-10-08.png%22%20alt%3D%222020-10-08.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMeanwhile%2C%20this%20is%20how%20we%20need%20that%20data%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222020-10-08%20(1).png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F225165iF34803668F1F3F5E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%222020-10-08%20(1).png%22%20alt%3D%222020-10-08%20(1).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAt%20the%20moment%2C%20we're%20putting%20the%20column%20with%20all%20the%20orders%20into%20our%20already%20filled%20out%20template%20with%20all%20the%20product%20columns%20as%20in%20the%202nd%20photo%2C%20then%20using%20this%20formula%20to%20copy%20that%20data%20into%20the%20next%20column%3A%26nbsp%3B%3DSUBSTITUTE(A2%2CCHAR(10)%2C%22%5E%22)%3C%2FP%3E%3CP%3EAnd%20then%20we%20do%20a%20text%20to%20columns%2C%20delimited%2C%20with%20tab%20and%20%5E%3C%2FP%3E%3CP%3EBut%20that%20only%20gets%20us%20here%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222020-10-08%20(2).png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F225169iEDF6619D71912413%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%222020-10-08%20(2).png%22%20alt%3D%222020-10-08%20(2).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAnd%20I%20still%20have%20to%20spend%20hours%20inserting%20cells%20to%20move%20the%20data%20to%20the%20right%20column%20and%20then%20find%2Freplacing%20text%20with%20nothing%20so%20we%20can%20just%20have%20the%20amount%20ordered%20data%20in%20each%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20my%20question%20is%20does%20anyone%20know%20of%20a%20formula%20or%20macros%20or%20something%20that%20will%20make%20this%20easier%20for%20our%20team%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3CP%3EAdrienne%3C%2FP%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1760127%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1761775%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Data%20from%20one%20cell%20into%20multiple%20columns%3F%20Help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1761775%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F825104%22%20target%3D%22_blank%22%3E%40AdrienneEC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20job%20for%20Power%20Query%20if%20you%20consider%20it%20as%20an%20option.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1781099%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Data%20from%20one%20cell%20into%20multiple%20columns%3F%20Help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1781099%22%20slang%3D%22en-US%22%3EThanks!%26nbsp%3BI%20have%20been%20trying%20to%20play%20around%20with%20that%20to%20figure%20out%20what%2Fhow%20to%20do%20this.%20Any%20tips%3F%20I've%20never%20used%20Power%20Query%20before%20and%20it's%20a%20lot%20for%20me%20to%20take%20in%20at%20once.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor
 
 
I have an order form online that does everything we want it to do except spit out a report that makes any sense for us to use when it comes to placing our orders. And they haven't offered much in terms of helpful solutions, so I figured I would try here.

 

Basically, the problem is that in the excel report that comes from the form, every person's order goes into one cell:

2020-10-08.png

 

 

 

 

 

 

 

 

 

Meanwhile, this is how we need that data:

2020-10-08 (1).png

At the moment, we're putting the column with all the orders into our already filled out template with all the product columns as in the 2nd photo, then using this formula to copy that data into the next column: =SUBSTITUTE(A2,CHAR(10),"^")

And then we do a text to columns, delimited, with tab and ^

But that only gets us here:

2020-10-08 (2).png

And I still have to spend hours inserting cells to move the data to the right column and then find/replacing text with nothing so we can just have the amount ordered data in each cell.

 

So my question is does anyone know of a formula or macros or something that will make this easier for our team? 

 

Thanks in advance!

Adrienne

 
 
 
3 Replies
Highlighted

@AdrienneEC 

That's job for Power Query if you consider it as an option.

Highlighted
Thanks! I have been trying to play around with that to figure out what/how to do this. Any tips? I've never used Power Query before and it's a lot for me to take in at once.
Highlighted

@AdrienneEC 

That's better to explain on sample file, otherwise it'll be abstract repeating of books.