ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Split columns in excel without using any Microsoft excel templates or vb-script: How to split data contents from excel?

Updated on July 4, 2013

Split columns in excel without using any Microsoft excel templates or vb-script with excel formulas.Spreadsheet in Microsoft Excel can easily split into different column cells. When we need to convert from one column (that has several values separated by commas or semicolons or tabs) to two or more columns, we can split those into several columns. For example, we have an excel file which has a column "Employee Name" containing values first name, last name and separated by commas. Such type of situation, we need to split cells two different cells (fist name and last name columns). Yes! this is easily possible to split columns in excel. Following this tutorial, you can easily split data from one column to multiple columns. The tutorial is created using Microsoft excel 2013. However, this tutorial is applicable to other version also like Excel 2010 / Microsoft excel 2007.

Steps to split data from excel cell to multiple cells in excel spreadsheet

To split data from excel cell to multiple cells in excel spreadsheet, follow the following instructions step by step:

Step 1:Opening Microsoft Excel file

Open the excel document that needs splitting data from one cell to multiple cells. For example, we have an excel sheet, MyExcelDocuments.xlsx, contains a simple table and we just opened this. After opening the Excel file, we see the following data are included with our Excel document. In our excel file, we have one column data containing Employee names. Employees name has two parts: first name and last name separate with a comma in between them.

Step 2: Selecting the column

Select the data column that needs to split. You may also select some data from the column instead of selecting the entire column. In our example tutorial, we select the entire column to split.

Step 3: Starting the splitting technique

Splitting cells in excel is done using Text to Columns under Data tab. So, click on Data tab form the Excel ribbon. Data tab has several buttons and functions. One of the buttons / options in Data tab is Text to Columns option. Press on it and it will pop-up a window.

Step 4: Choosing a Delimiter

After completing Step 3, Convert Text to Columns Wizard window will be appeared. We have to define the Original data type between Delimited and Fixed width. We choose, Delimited option as delimited option works when there are commas, tabs. So, choose the Delimited option and click on Next button.

Step 5: Specifying delemiters

In this step, we have to choose the Delimiters on which we would like to split. Here, we choose Comma as our file is separated by commas. If your file is separated with other delimiters then choose that delimiter. After choosing delimiters, click on Next button.

Step 6: Column data formatting

Choose the column data format option as Text. You will see that your table column data is now divided between columns in the Data preview section of Convert Text to Columns Wizard window. Now, click on Finish button to complete the splitting of cells into multiple columns.

Step 7: Observing the columns

We will see that our one column is divide between two columns. For our one column excel example, we will see the following figure:

Step 8: Finalizing table column header

If your column header has the exact format, then you may skip this step. Otherwise, you have to work in this step i.e. you have to format the column header if necessary. For our example, we have one column and the header is Employee name. After splitting, we have two columns and the columns header is not properly meaningful. We have two column and the first column contains Employee first name and the second column contains Employee last name. We just change the column name and after changing it may look like the following:

Following the above steps, you can easily split data contents from excel cell to multiple cells. To split columns in excel, we simply use Text to Columns method without using any Microsoft excel templates or vb-script in this tutorial. Hope, you can do this easily. If you face any difficulties for this type of wok in Excel, kindly note that in the comment section and i try to help you.


    0 of 8192 characters used
    Post Comment

    • jabelufiroz profile image

      Firoz 3 years ago from India

      Impressive hub. Voted up.

    • profile image

      C. C. GORE 3 years ago


    • profile image

      TARUN VAISH 3 years ago

      This was really helpful. Was about to use VBA for this. Saved my time. Thanks!

    Click to Rate This Article