Excel: How to Split Data in One Column to Multiple Columns

Excel text to column

In some cases, the files we download from other applications does not come in a format that will enable us to analyse and present reports in Excel. For instance, if you copy information from a text file into excel all the data may appear in one column as shown below.

Excel text to column

In this above situation, we have to first clean the data by splitting the information into different columns.

Proposed Solution

In many cases, you can eliminate the use of formulas and use Excel’s Text-To-Columns command to split strings into their parts. This command is found in the Data Tools group of the Data tab.

Here we select Data > Text to Columns > Delimited > Next.

  1. Select the list, then go to Data tools and Choose Text to Columns option
  2. Choose delimited
  3. Excel would propose a delimiter. Choose Space and click on Next option
  4. Click Finish

You can also learn using the Video below:

Comment

There are other options like MID, LEFT and RIGHT function. You can combine them with FIND and LEN function to create similar text splitting. However, the above method is faster particularly for less frequent events.

PREVIOUS TRAINING PARTICIPANTS

Leave a comment

Your email address will not be published.


*