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 which you want to analyze independently. Suppose one column contains address 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 contain specific information associated with a certain row. For example, one column might contain the customer’s name, Customer’s DOB, their 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 below steps to split cell in excel these names into the First Name & Last Name separately.
- Select the whole cells range which you want to split. Here we have selected the range from A3:A10. Refer below screenshot.
- Go to the DATA tab.
- Click on Text to Columns option under Data Tools section. See below screenshot.
- It will open a Convert Text to Column dialog box like below screenshot.
Let us elaborate 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 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 below screenshot.
- Click on Next button.
- The 2nd step screen will appear.
- Select the checkbox for Space as the delimiter under Delimiters section and deselect other option here (if selected). Refer below screenshot.
- Click on Next button.
- The 3rd screen will appear here. In this step, you can specify the destination where you want to see the result. The default destination here is 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 Finish button.
The final result is shown below:
Method #2 – Split Cells in Excel Using Fixed Width Option
If you want to split the text at the particular point. Use this option.
We have given the list of Date & time and we need to split this list.
Follow below steps:
- Select the cells range.
- Go to the DATA tab.
- Click on Text to Columns option under Data Tools section.
- It will open a dialog box to convert Text to Columns wizard. This wizard has 3 step process. Refer below screenshot. Select the Fixed Width option.
- Click on Next button.
- It will appear the 2nd step screen like below screenshot.
- Under Data Preview section, we can create a break line and place it at the desired position on the text where we want to split cell 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 cell in excel for given the list into two parts: Date & Time.
- Click on Next button and it will appear the 3rd step process.
- You can now select how to import each new column. Choose the data type under Column data format section as shown in below screenshot.
- Select the destination cell address where you want the results.
- Click on 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 in 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 to CSV
- VBA in Cells
- Shortcut Key to Merge and Center in Excel
- Shortcut Key to Merge Cells in Excel
- Removing Spaces in Excel Cell
- Convert Excel Text to Numbers
- How to Combine Cells in Excel?
- How to Merge Cells in Excel?
- How to Unmerge Cells in Excel?
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
