Nehad Ulfat
In Microsoft Excel, there are several techniques available to split cells into multiple parts. We can use formulas, tools, or even Power Query to divide a cell or column into two parts. In this article, you’ll learn different suitable methods to split cells or texts with simple illustrations.
Download Practice Workbook
You can download our Excel workbook that we’ve used to prepare this article.
5 Easy Ways to Split Cells in Excel
1. Split Cells in Excel with Text to Column Feature
In the following table, there are some random names lying in the Text column. What we’ll do here is split each name into two parts. The first part will be displayed in Cell 1 column and another part in Cell 2 column.
In our first method, we’ll use the Text to Column feature which is essentially used to split a text into different parts that were once delimited by symbols or spaces.
📌 Step 1:
➤ First, select the texts that you want to split into multiple cells.
📌 Step 2:
➤ From the Data ribbon, select the Text to Columns tool from the Data Tools group.
📌 Step 3:
➤ In the wizard as shown below, click Next.
📌 Step 4:
➤ From the Delimiters options, put a checkmark on Space only.
➤ Click Next again.
📌 Step 5:
➤ Now select the destination cell where you want to see the split texts.
➤ Press Finish and you’re done.
As shown in the screenshot below, you’ll find all names split into two parts in Cell 1 and Cell 2 columns.
Read More: VBA to Split String into Multiple Columns in Excel (2 Ways)
2. Insert and Merge Columns to Split Cells in Excel
Now we’ll see how an empty column under a header can be split into two parts. In the following table, we’ll divide the Name & Age column into two columns while keeping the header name unchanged. This method is useful when you have to work with a large table but suddenly you’ll need to split any column inside the data table.
📌 Step 1:
➤ Select the Gender column first.
➤ Right-click your mouse and launch the Context Menu.
➤ Choose the Insert option.
An additional column has been added to the right of the Name & Age column.
📌 Step 2:
➤ Now select the Name & Age header along with the blank header of the newly inserted column.
➤ From the Alignment group, click on the Merge & Center command.
So, now the Name & Age column has been divided into two parts. You can also keep the headers separated by not choosing the option to merge them. You have to simply edit two headers with the texts ‘Name’ and ‘Age’.
Similar Readings
- How to split a single cell in half in Excel (diagonally & horizontally)
- How to Make Two Lines in One Cell in Excel (4 Methods)
- How to Split a Cell into Two Rows in Excel (3 ways)
3. Apply Flash Fill Option to Divide Cells
Flash Fill tool is another interesting feature in MS Excel to return output in a range by following a specific pattern in a dataset. It saves a lot of time while working with a large data table. So let’s find out how we can split names from the Text column by using this application.
📌 Step 1:
➤ Select Cell C5 and type the first name of George Blewett from Cell B5.
📌 Step 2:
➤ Use Fill Handle to autofill the entire column.
➤ From the AutoFill menu, choose the Flash Fill option.
The Cell 1 column is now showing all the first names from the Text column.
📌 Step 3:
➤ Follow the similar procedures for the Cell 2 column.
And all the names from the Text column are now split into two parts as displayed in the following screenshot.
Read More: Excel Split Cell by Delimiter Formula
4. Use Formulas to Split Cells in Excel
We can split cells By using LEFT, RIGHT, and FIND functions too. The LEFT and RIGHT functions pull out the specified numbers of characters from the left and right of a text respectively. The FIND function looks for a specific character or text in a cell and returns the starting position of that character or text.
Let’s go through the following steps now to extract first and last names separately with formulas.
📌 Step 1:
➤ Select Cell C5 and type the following formula:
=LEFT(B5,FIND(“ “,B5)-1)
Press Enter and you’ll get the first name of ‘George Blewett’.
🔎 How Does the Formula Work?
- FIND(” “,B5): The FIND function looks for the space character (“ “) in Cell B5 and returns the position of that character which is ‘7’.
- FIND(” “,B5)-1: After subtracting 1 from the previous result, the new return value here is ‘6’.
- LEFT(B5,FIND(” “,B5)-1): Finally the LEFT function extracts the 1st 6 characters from the text in Cell B5 which is ‘George’.
📌 Step 2:
➤ Now select Cell D5 and type the following formula:
=RIGHT(B5,LEN(B5)-FIND(" ",B5))
And after pressing Enter, we’ll get another part of the name in the corresponding output cell.
🔎 How Does the Formula Work?
- LEN(B5): The LEN function counts the number of total characters found in Cell B5 and thereby returns ‘14’.
- FIND(” “,B5): The FIND function here again looks for the space character in Cell B5 and returns the position which is ‘7’.
- LEN(B5)-FIND(” “,B5): This part of the entire formula returns ‘7’ which is the subtraction between the previous two outputs.
- RIGHT(B5,LEN(B5)-FIND(” “,B5)): Finally, the RIGHT function pulls out the last 7 characters from the text in Cell B5 and that is ‘Blewett’.
📌 Step 3:
➤ Now select both Cells C5 and D5.
➤ From Cell D5, use Fill Handle to drag down to the last output Cell D9.
And the result is now visible as shown in the following.
Read more: Excel Formula to Split: 8 Examples
5. Split Cells Using Excel Power Query
Power Query is another excellent feature in MS Excel to split or divide cells into multiple columns. Let’s find out how we can use Power Query to meet our objectives. We’ll work with the similar dataset and split the surnames into first and last parts.
📌 Step 1:
➤ First, select the surnames from the entire column including the header.
➤ Go to the Data tab and choose the option ‘From Table/Range’ in the ‘Get & Transform Data’ group.
📌 Step 2:
➤ In the ‘Create Table’ dialog box, put a checkmark on the ‘My table has headers’ option.
➤ Press OK.
The Power Query Editor will open up where we’ll find our selected data as displayed below.
📌 Step 3:
➤ Now put your mouse pointer on the header ‘Text’.
➤ Open the Context Menu by right-clicking your mouse.
➤ From the Split Column sideway drop-down, select the option ‘By Delimiter’.
📌 Step 4:
➤ Specify the delimiter ‘Space’ as shown inside the highlighted border since the texts in our dataset are delimited by spaces
➤ Press OK and you’re done.
In the Power Query window, you’ll find the selected texts in two parts now. And we have to copy these split texts to our Excel spreadsheet.
📌 Step 5:
➤ From the Close & Load drop-down, choose the option ‘Close & Load to…’.
📌 Step 6:
➤ In the Import Data dialog box, select the output cell from where the split texts along with their headers will be displayed.
➤ Press OK.
And the following table is the final output that we’ll obtain by using the Power Query editor.
Read More: How to Split One Cell into Two in Excel (5 Useful Methods)
Concluding Words
I hope all of the methods mentioned in this article will now help you to apply them in your Excel spreadsheets when you need to split cells into multiple columns. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.
