Making a table in Excel requires you to put different types of data in different columns. But sometimes the data collected by others or uploaded from a TXT file may not be organized that well. You can of course split the data in a column into different columns manually by cutting and pasting. But if there’re tons of data waiting to be processed, you must want an easier way to save time and effort. I’ve introduced the method to split a column through Text to Columns in previous post, here I’d like to share another way using Flash Fill.

In this table below, all the data are stored in column A which looks very messy. They can obviously be divided into three types: serial number, name and score. To split the data in column A to column B-D separately, the first thing you should do is giving Excel an example.

Enter the first data of the first row in B2, which is “1“.

Put the cursor at the bottom-right corner of B2 and drag your mouse to select all the cells in column B. Thus you can auto fill the selected cells with fill handle.

Then click the icon of Auto Fill Options on the lower-right corner and select Flash Fill in the drop-down list.

This column will automatically be filled with the same type of data as “1” in column A.

Similarly, you can enter “Nick” in C2 and use Flash Fill to enter all the names in column C; enter “70” in D2 and auto fill all the cells in column D with the scores.

In this way you can easily split the text in a column into different columns no matter how much the data is.