How to convert data from multiple columns to one column

Converting data from multiple columns to one column is a common requirement and we struggle to get this done. Very often we would have to resort to manual cut and paste. Here is how you can do it very quickly…

Estimated Reading Time 3 min

The Need

Let us say I have this type of data in Excel or a Word table …

… and you want to have all these countries in a single column.

Trying to do this in Excel itself will require multiple copy paste operations.

There is a faster way available.

The solution

Word is better at handling tables. So we take the data temporarily to Word and convert it quickly.

Copy it from Excel and Paste it in Word. By default it will be pasted as a table.

Now click inside the table and go to the Table tools tab in the ribbon – Layout tab.

Click on Convert Table to Text.

This dialog is asking you what to put in place of each column. It will put a delimiter like comma, Tab, etc. to separate out each column. At the end of the row it will anyway start a new paragraph.

But we want all items on a new line. Therefore, we choose Paragraph Marks as a delimiter for columns. Click Ok and your job is done!

Removing Blanks

As the original columns had different number of entries, there will be some blank rows which creep in.

Paste in Excel – select the data, Choose Go To (F5) – Special – Blanks. This will select all blanks. Now right click in the blanks and choose Delete… Shift Cells Up.

That’s it.

You could also use Power Query to remove the blanks.

The entire process could also have been done through Power Query. But I still find this approach simpler unless the data is very large and difficult to handle in Word.

***