How to Split Cells in Excel (5 Easy Tricks)

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.

Split Cells in Excel with Text to Column Feature

📌 Step 1:

➤ First, select the texts that you want to split into multiple cells.

Split Cells in Excel with Text to Column Feature

📌 Step 2:

➤ From the Data ribbon, select the Text to Columns tool from the Data Tools group.

Split Cells in Excel with Text to Column Feature

📌 Step 3:

➤ In the wizard as shown below, click Next.

Split Cells in Excel with Text to Column Feature

📌 Step 4:

➤ From the Delimiters options, put a checkmark on Space only.

➤ Click Next again.

Split Cells in Excel with Text to Column Feature

📌 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.

Insert and Merge Columns to Split Cells in Excel

📌 Step 1:

➤ Select the Gender column first.

➤ Right-click your mouse and launch the Context Menu.

➤ Choose the Insert option.

Insert and Merge Columns to Split Cells in Excel

An additional column has been added to the right of the Name & Age column.

Insert and Merge Columns to Split Cells in Excel

📌 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.

Insert and Merge Columns to Split Cells in Excel

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


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.

Flash Fill Option to Divide Cells

📌 Step 2:

➤ Use Fill Handle to autofill the entire column.

➤ From the AutoFill menu, choose the Flash Fill option.

Flash Fill Option to Divide Cells

The Cell 1 column is now showing all the first names from the Text column.

Flash Fill Option to Divide Cells

📌 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)

Formulas to Split Cells in Excel

Press Enter and you’ll get the first name of ‘George Blewett’.

Formulas to Split Cells in Excel

🔎 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))

Formulas to Split Cells in Excel

And after pressing Enter, we’ll get another part of the name in the corresponding output cell.

Formulas to Split Cells in Excel

🔎 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.

Split Cells Using Excel Power Query

📌 Step 2:

➤ In the ‘Create Table’ dialog box, put a checkmark on the ‘My table has headers’ option.

➤ Press OK.

Split Cells Using Excel Power Query

The Power Query Editor will open up where we’ll find our selected data as displayed below.

Split Cells Using Excel Power Query

📌 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’.

Split Cells Using Excel Power Query

📌 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.

Split Cells Using Excel Power Query

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.

Split Cells Using Excel Power Query

📌 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.


Related Articles

Nehad Ulfat

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo