How to convert data in one column to two columns

< Back to Search results

 Category: [Excel]  Demo Available 

How to convert data in one column to two columns




The user has data exported but data is in only in one column. There are 2 types of data (email and No of emails sent) and follow each other:
[email protected]
[email protected]
[email protected]

The user would like to convert this convert into two columns, so that the user can make table and reports from this data. 





    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Number", each Value.Is(Value.FromText([Column1]), type number)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if [Number] = true then [Column1] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom1",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Number] = false)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Number"})
    #"Removed Columns"


If anyone needs a formula solution, try this:

With your data in Sheet1, column A, then on Sheet 2 create these formulae:

A1: =INDEX(Sheet1!A:A,ROW()*2-1,1)
B1: =INDEX(Sheet1!A:A,ROW()*2-,1)

Copy these down as far as needed, until 0's appear.


Obtained from the OzGrid Help Forum.

Solution provided by AlanSidman (code) and mikeopolo (formula).


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

How to combine rows with the same ID# but different columns


How to sum up columns in each row and highlight until that value
How to use Excel VBA macro to convert multiple columns to multiple rows


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)