CODEWITHSUNDEEP
excelexcel-formula
RTC
RTC

Reputation: 23

Excel - Side by Side Columns

I would like to take a single column in Excel of data and spread it across multiple columns. Instead of having one very long single column of data, I want to have that spread across multiple columns side by side. I am not looking to separate any data in each cell to another as in Text to Columns.

Once the data is side by side, I can copy it into my SQL editor where the format will be better. See the image as an example. What function in Excel allows this instead of manually copying and pasting? Thank you

Excel Image

Upvotes: 1

Views: 472

Answers (1)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27233

Using WRAPCOLS()

enter image description here

• Formula used in cell C1

=WRAPCOLS(A1:A50,10)

One can use Power Query as well to accomplish this task, adding as an alternative approach.

enter image description here

• Select some cell in your Data Table,

• Data Tab => Get&Transform => From Table/Range,

• When the PQ Editor opens: Home => Advanced Editor,

• Make note of the Table Name,

• Paste the M Code below in place of what you see.

let
    Source = Excel.CurrentWorkbook(){[Name="Table29"]}[Content],
    Column1 = Table.FromRows(List.Split(Source[Column1],10)),
    #"Transposed Table" = Table.Transpose(Column1)
in
    #"Transposed Table"

enter image description here

• Importing it back into Excel press Home => Close => Close & Load To, when importing, you can either select Existing Sheet with the cell reference you want to place the table or you can simply click on NewSheet

enter image description here

Upvotes: 2

Related Questions