There can be situations when you have to split cells in Excel. These could be when you get the data from a database or you copy it from the internet or get it from a colleague.
A simple example where you need to split cells in Excel is when you have full names and you want to split these into first name and last name.
Or you get address’ and you want to split the address so that you can analyze the cities or the pin code separately.
As an Excel user, you may often come across data that is all clumped together in one column. This makes it very difficult to analyze or work with. The good news is splitting cells into separate columns in Excel is super easy and can be done in just a few clicks!
In this comprehensive guide I will walk you through the various ways to split cell contents into different columns using Excel’s built-in tools.
Whether you are working with delimited data or fixed-width data this tutorial has got you covered. By the end you will be splitting cells like a pro! Let’s get started.
Why Split Cells in Excel?
Here are some common scenarios where splitting cell data into columns can be very useful:
-
Importing data – Data imported from another source like CSV or website often lands into Excel smashed together in one column. Splitting it makes the data structured and readable.
-
First and last names – Full names stored together need to be separated into first and last for better sorting and visualization.
-
City and country – Location data with city and country together can be bifurcated into two columns for analysis.
-
Product details – Product names with size, color etc. can be divided into separate columns for inventory management.
-
Fixed-width data – Data consisting of fixed-width values like codes, IDs can be split based on character length.
The key advantage of splitting columns is it transforms jumbled-up data into a relational structure that can be easily filtered, sorted, analyzed and visualized.
Method 1 – Using Text to Columns
This is one of the easiest ways to divide a column into multiple columns in Excel. The Text to Columns feature can split data based on a delimiter (like comma or space) or based on fixed width. Let’s see how it works:
-
Select the column you want to split by clicking the column header.
-
Go to Data tab > Data Tools group > Click Text to Columns.
-
In the Convert Text to Columns wizard, select Delimited or Fixed width as per your data. Click Next.
-
For delimited data, check the delimiter your data contains like comma, space etc.
-
For fixed width data, drag dividers in the data preview to set column break points.
-
Click Next, select destination to place the split data and click Finish.
The data will instantly be divided into different columns as desired. This method takes just a few clicks to split any type of data in Excel.
Method 2 – Using Flash Fill
Flash Fill is another fast way to split column data into multiple columns. Although not as flexible as Text to Columns, it can intelligently split based on a pattern with a couple of clicks.
Here are the steps to use Flash Fill to split columns:
-
Insert a new column beside the one containing data to be split.
-
In the new column, type the split data for the first 2-3 rows manually.
-
Select the rows and click Home > Fill > Flash Fill (or use shortcut Ctrl + E).
-
Flash fill will automatically detect the pattern and split the remaining column data based on your example.
The key benefit of Flash Fill is it automatically does the heavy lifting once you provide a couple of examples. It saves a ton of manual work.
Method 3 – Using Formulas
Excel formulas can also be used to split textual data into different columns. While not as direct as Text to Columns, some formulas can extract substrings from text strings and divide into columns.
Some helpful formulas are:
LEFT– Extracts text from left of cellRIGHT– Extracts text from right of cellMID– Extracts text from middle of cellSEARCH– Finds position of textLEN– Returns text length
These formulas can be combined to identify and extract substrings from data and output into separate columns. This method requires knowledge of Excel formulas but provides more flexibility.
Method 4 – Using Power Query
Power Query is an advanced Excel tool to transform data. It can be used to split columns in various ways:
-
Using split column function – Split text by delimiter like comma, space etc.
-
Specifying column widths – Set fixed widths to divide into equal columns
-
Conditional splits – Split by values satisfying a condition
While Power Query requires learning an additional skillset, it provides very nuanced control over splitting data into columns. This method is best for complex data transformations.
Things to Remember
Here are some best practices to keep in mind when splitting columns in Excel:
-
Be clear about the exact divider or split logic before applying any method
-
Split into separate adjacent columns to avoid overwriting original data
-
Examine some split results manually to catch any wrong columns
-
Format split columns as text/numeric as appropriate
-
Add/delete columns if needed once data is split
-
Split again into more columns if required in multiple rounds
Splitting cramped messy data into readable columns is a vital Excel skill. In this article we explored 4 easy methods to divide cell contents into multiple columns:
-
Text to Columns – Fast split by delimiter or fixed width
-
Flash Fill – Intelligent split based on pattern
-
Formulas – Extract substrings using Excel functions
-
Power Query – Advanced conditional splitting
So next time you need to split cells into columns, use one of these techniques based on your exact requirements. With some practice, you’ll be able to tidy up your Excel data in no time!
Split Cells in Excel Using Text to Column
Below I have a list of names of some of my favorite fictional characters and I want to split these names into separate cells.:
Here are the steps to split these names into the first name and the last name:
- Select the cells in which you have the text that you want to split (in this case A2:A7).
- Click on the Data tab
- In the ‘Data Tools’ group, click on ‘Text to Columns’.
- In the Convert Text to Columns Wizard:
- Step 1 of 3 of Text to Columns Wizard: Make sure Delimited is selected (it is the default selection). This would allow you to separate the first name and the last name based on a specified separator (space bar in this case).
- Click on Next.
- Step 2 of 3 Text to Columns Wizard: Select Space as the delimiter and deselect everything else. You can see how your result would look like in the Data preview section of the dialog box.
- Click on Next.
- Step 3 of 3 Text to Columns Wizard: In this step, you can specify the data format and where you want the result. I will keep the data format as General as I have text data to split. The default destination is A2 and if you continue with this, it will replace the original data set. If you want to keep the original data intact, select another cell as the destination. In this case, B2 is selected.
- Click on Finish.
- Step 1 of 3 of Text to Columns Wizard: Make sure Delimited is selected (it is the default selection). This would allow you to separate the first name and the last name based on a specified separator (space bar in this case).
This will instantly split the cell’s text into two different columns.
Note:
- Text to Column feature splits the content of the cells based on the delimiter. While this works well if you want to separate the first name and the last name, in the case of first, middle, and last name it will split it into three parts.
- The result you get from using the Text to Column feature is static. This means that if there are any changes in the original data, you’ll have to repeat the process to get updated results.
How to Split Cells in Excel
In this tutorial, you’ll learn how to split cells in Excel using the following techniques:
- Using the Text to Columns feature.
- Using Excel Text Functions.
- Using Flash Fill (available in 2013 and 2016).
Split data into different columns in Microsoft Excel
How do I split a text to a column in Excel?
Choose the entire column that you want to split. Click on Data Tools. Select Text to Columns. The Convert Text to Columns Wizard will appear. Choose the option that best describes your data (e.g., delimited or fixed width ). Click Next. If you select Delimited pick the delimiters (e.g., comma, space). Preview your data in the Data preview window.
How to split a cell into multiple cells in Excel?
To split them into different cells: Step 1) Select the cell content to be split into multiple cells. Step 2) Go to the Data tab > Data tools > Text to Column. Clicking on it, you will get to see the Convert Text to Columns Wizard dialog box. Step 3) Select Delimited and click the Next button.
How to split data into multiple columns using Excel functions?
We’ll use the following data set to split data into multiple columns using Excel functions. Excel provides the Text to Columns feature in the Data tab. Text to Columns features allows split data into columns separated by comma, and space that are delimiters or separating indicators. Choose the entire column that you want to split.
How to split data into a different column using delimited text?
We want to split each attribute into a different column using delimited text. So to split the data: Select the original data set. Select the Text to Columns option. The Convert Text to Columns Wizard appears. Select Delimited text from the Original Data Type. Press Next. Choose the required delimiter – we selected a comma.
