Split Cells in Excel

Splitting cells in excel means splitting the data present in excel in two or more than two parts in another column, splitting is done by text to columns in excel where we have two options as delimited which uses a delimiter to split the cells and fixed-width where we define the width from we want to split the cells, text to columns can be accessed from the data tab or keyboard shortcut ALT+ A+ E.

How to Split a Cell in Excel

Sometimes you need to split cells in excel if they contain multiple sets of data that you want to analyze independently. Suppose one column contains addresses, including street addresses and Zip codes.

You have seen most of the time while working on Excel that data is organized into columns, and each column contains specific information associated with a certain row. For example, one column might contain the customer’s name, Customer’s DOB, contact number, address, etc.

In this article, we will learn how to split cells in excel using Text to Column Method

Split Cells in Excel Using Text to Column method

We have given some list of names, including Full name.

Follow the below steps to split cells in excel these names into the First Name & Last Name separately.

• Select the whole-cell range which you want to split. Here we have selected the range from A3:A10. Refer to the below screenshot.

• Go to the DATA tab.
• Click on the Text to Columns option under the Data Tools section. See the below screenshot.

• It will open a Convert Text to a Column dialog box like the below screenshot.

Let us elaborate on the two ways – Delimited Option and Fixed Width.

Method #1 – Split Cells in Excel Using Delimited Option

• This is the 3 step process.
• Make sure the Delimited option is selected (It is by default selected here). This option allows you to separate the first name and the last name based on a specified separator. In our example, the space bar is a separator here. Refer to the below screenshot.

• Click on the Next button.
• The 2^nd step screen will appear.

• Select the checkbox for Space as the delimiter under the Delimiters section and deselect other options here (if selected). Refer to the below screenshot.

• Click on the Next button.
• The 3^rd screen will appear here. In this step, you can specify the destination where you want to see the result. The default destination here in Cell A4 as shown in below screenshot.

• If you continue with the default destination cell address, it will replace the original data set. If you want to keep the original dataset as it is, selects another cell as the destination.
• In this example, B4 is selected.
• Click on the Finish button.

The final result is shown below:

Method #2 – Split Cells in Excel Using Fixed Width Option

Suppose you want to split the text at a particular point. Use this option.

We have given the list of Date & time, and we need to split this list.

Follow the below steps:

• Select the cell’s range.
• Go to the DATA tab.
• Click on the Text to Columns option under the Data Tools section.

• It will open a dialog box to convert Text to Columns wizard. This wizard has 3 step process. Refer to the below screenshot. Select the Fixed Width option.

• Click on the Next button.
• It will appear on the 2^nd step screen like the below screenshot.

• Under the Data Preview section, we can create a broken line and place it at the desired position on the text where we want to split cells in excel.
• By double-clicking on the line, we can remove the break line too.
• Now we will place the break line according to our desired results.
• As in our example, we want to split cells in excel forgiven the list into two parts: Date & Time.
• Click on the Next button, and it will appear in the 3^rd step process.
• You can now select how to import each new column. Choose the data type under the Column data format section, as shown in the below screenshot.
• Select the destination cell address where you want the results.
• Click on the finish.

The final result is shown below:

Things to Remember about Split Cell in Excel

• Text to Column feature splits the text of the cells based on the delimiter.
• The result of the method “Text to Column” is static. If you make any changes to the original data, it won’t impact the results. You need to repeat the whole process again for the desired results.

Recommended Articles

This has been a guide to Split a cell in Excel. Here we discuss how to split a cell in excel using text to column (Delimited and Fixed Width method) along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

• Convert Excel Text to Numbers
• Combine Cells in Excel
• Merge Cells in Excel
• Unmerge Cells in Excel
• Change Case in Excel