How to Split columns in excel into multiple columns

Want to split the columns in Excel? In this article, we are going to show you two methods one using Microsoft 365 Excel and the other method using the older version of MS Office Excel.




While you are working with excel then there may be a need to split the columns. Then you must know how to do it. Do you know how to split columns in excel? If your answer is “No” then this article will help you a lot. Here I am giving the simple procedure to Split columns in excel into multiple columns. Suppose cells in a column of your excel sheet have a first name, last name, and date in a single cell separated with comma or tab or semicolon or etc. You can easily convert them into multiple columns.

1.How to split columns in excel into multiple columns Using Microsoft 365?

How to Split columns in excel into multiple columns 1

In this section, we will see how to split cells in a single column into multiple columns.

Total Time: 4 minutes

Step 1: Open Excel and enter data in same column




How to Split columns in excel into multiple columns 2

Firstly, open the Excel sheet in Microsoft 365. In that you can see number cells with A, B, C, ……. horizontally which represent columns and 1, 2, 3, …. vertically which represent rows. Enter any data in the same cell as shown in the above image.

Step 2: Click on Data

How to Split columns in excel into multiple columns 3

As a part of the Excel sheet editing, viewing you can see a list of options in the menu bar to make your excel sheet more stylish and easy to understand.
In the menu bar, you can see a Data menu in the fifth position from the left side of the screen. Click on that Data option.

Step 3: Click on Text to Column




How to Split columns in excel into multiple columns 4

In the Data menu, you can see different options like Sort Ascending, Sort Descending, Custom sort, Filter, Flash fill, etc. On that screen, you can also see Text to Columns. Click on that Text to Columns.

Step 4: Select Delimiters

How to Split columns in excel into multiple columns 5

When you click on the Text to Columns, you can see the above screen. You need to select a Delimeter from the available options like Tab, Semicolon, Common and space. Now you need to select Custom from the list. You can see the preview also. And then Click on Apply as shown in the above image.

Step 5: Result in different columns

How to Split columns in excel into multiple columns 1

Once you click on Apply option, then you can see the data of same cell split into different columns as shown in the image.
This is the procedure for splitting the same cell data into different columns.




Estimated Cost: 9.99 USD

Supply:

  • Microsoft 365 Excel

Tools:

Materials: software

2. Split columns in excel into multiple columns in Older version of MS Office Excel

Video Tutorial

We provided this article in the form of a video tutorial for our reader’s convenience. If you are interested in reading, you can skip the video and start reading.




  • Open your excel sheet.
  • Select the entire column which you want to split. You can use a mouse or shift + down arrow to select the entire row.
excel split cell into columns
  1. Click on “Data” in the top menu.
  2. Click “Text to Columns” shows that in the above screenshot. You will see another window shows that in the below screenshot.
excel split cell
  1. Make sure you have selected “Delimited”.
  2. Check the preview of your selected date in the rectangular box. If anything goes wrong, you c an reselect again.
  3. Click “Next”. You will see another window shows that in the below screenshot.
excel split column
  1. Check any one of the options available under “Delimiters“. Here, I have selected “comma” because my data was separated with “comma”. If your data is separated with semicolon or tab or space you need to select options according to your data.
  2. Check your preview in the “Data preview” box.
  3. Click “Next” if you like the preview. You will see another window shows that in the below screenshot.
split columns in excel

Check “Data Preview”, you will see your output columns. Click on the first column. Check the corresponding format under “Column Data Format”. In this example, the first column contains the first name which is a text. So I have selected “Text” under “Column Data Format”. The second column is also text. The third column is the date, so I have selected “Date”. Likewise, you can select a data format for all columns.

Now you need to select destination fields for your output. Click on the symbol right to the “Destination” field shows that in the above screenshot. You will see a small rectangular window shows that in the below screenshot.

splitting columns in excel
  1. Click on any cell and use shift + right arrow mark to select cells where you want to place output data.
  2. You can check the selected columns in the wizard. Here I have selected cells B1, C1, and D1. Because my output has three columns. You can see the selection in the wizard B1 to D1.
  3. Click on the same symbol in the right bottom corner to close this wizard. You will go back to the main wizard.

Click “Finish” on the main wizard. You will see the output shows that in the below screenshot.

excel split first and last name

Here I have selected cells from B1 to D1. So my original data stays in A cell. If you had selected cells from A1 to C1 you will not see the original data. That will be overwritten by the output result.

Thanks for reading my article about Split columns in excel into multiple columns. If you like it, do me favor by sharing it with your friends and follow WhatVwant on Facebook and Twitter for more tips. Subscribe to whatVwant channel on YouTube for regular updates.

Comments Rating 0 (0 reviews)



Leave a Reply

Sending

Your subscription could not be saved. Please try again.
Thanks for your interest. Please check your Mail inbox or Spam for the confirmation message and click on the confirm button.

WhatVwant Newsletter

Subscribe to our newsletter and receive latest tech. tips in your inbox.