Too much information in a single column can make your Microsoft Excel spreadsheet more difficult to check out. To improve it, you ought to think about splitting up your column using the “Text to Columns” or “Flash Fill” functions.

“Text to Columns” will change your single column with multiple columns utilizing the same information. “Flash Fill” will reproduce the data, splitting it into new, individual columns while leaving the initial column intact.

How to Use Text to Columns in Excel

Microsoft Excel consists of a special function that allows you to split up extra long columns. It does this by separating columns using delimiters, like commas or semicolons, which broke up the information.

The function works by using Text to Columns, which you can access from the “Data” tab in your Microsoft Excel ribbon bar.

To test this function, we’ll be utilizing a set of data (an employee list, revealing names, dates of birth, and other info) in a single column. Each section of information is in a single cell, separated by a semicolon.

You’ll need to choose the cells containing your data first (cells A1 to A12 in the example above).

From Excel’s “Data” tab, click the “Text to Columns” button found in the “Data Tools” section.

This will bring up the “Convert Text to Columns Wizard” window and enables you to start separating your data. From the options, select the “Delimited” radio button and click “Next” to continue.

By default, Excel will choose to try and separate your single column data by each tab it discovers. This is great, however for our example, we’re utilizing information that’s separated by semicolons.

Pick your delimiter option from the side menu. For our example, our picked delimiter is a semicolon.

You can see how the transformed data will look in the “Data Preview” section at the bottom of the menu.

As soon as you’re prepared, click “Next” to continue.

You’ll now require to set the cell types for each column. For example, if you have a column with dates, you can set the proper date format for that column. By default, each column will be set to the “General” setting.

Using this choice, Excel will attempt to set the information type for each column instantly. To set these manually, click on your column in the “Data Preview” area. From there, pick the appropriate information type from the “Column Data Format” area.

If you want to avoid a column totally, select your column, then pick the “Do Not Import Column (Skip)” option. Click “Finish” to start the conversion.

Your single column will separate each area, using the delimiters, into specific columns using the cell format options you selected.

How to Use Flash Fill in Excel

If you ‘d like to keep your original data undamaged, however still separate the data, you can use the “Flash Fill” function rather.

Utilizing our employee list example, we have a single column (column A) header row, with a semicolon delimiter separating each little bit of information.

To use the “Flash Fill” function, start by typing out the column headers in row 1. For our example, “Employee ID” would go in cell B1, “First Name” in cell C1, etc.

. For each column, choose your header row. Start with B1 (the “Employee ID” header in this example) and after that, in the “Data Tools” area of the “Data” tab, click the “Flash Fill” button.

Repeat the action for each of your header cells (C1, D1, etc) to instantly fill the new columns with the matching information.

If the information is formatted properly in your initial column, Excel will immediately separate the material using the original header cell (A1) as its guide. If you get an error, type the following worth in the series in the cell listed below your header cell, then click the “Flash Fill” button again.

In our example, that would be the first data example in cell B2 (“101”) after the header cell in B1 (“Employee ID”).

Each new column will fill with the information from the initial column, using the preliminary first or 2nd rows as the guide to select the right data.

In the example above, the long column (column A) has been separated into 6 brand-new columns (B to G).

Due to the fact that the layout of rows 1 to 12 is the same, the “Flash Fill” function is able to copy and separate the data, utilizing the header row and first little bit of data.

“Text to Columns” will change your single column with multiple columns using the exact same data. If you have a column with dates, you can set the proper date format for that column. Using this choice, Excel will attempt to set the information type for each column immediately. If you want to avoid a column completely, select your column, then select the “Do Not Import Column (Skip)” alternative. If the data is formatted properly in your initial column, Excel will immediately separate the content using the original header cell (A1) as its guide.