How to Split a Cell in MS Excel
Microsoft Excel includes several commands and functions designed to make it easier to manipulate your spreadsheet data. One example is the "Text to Columns" wizard, which helps you split data from one cell into two or more new cells. This can be helpful when a text file does not import properly and all of the data is compacted into a single cell instead of spread out into the correct columns. Instead of importing the file again, you can simply use "Text to Columns" to separate it into columns.
Start MS Excel and open the file that contains the cells you need to split. Select the cells to be split. You may only split the contents of one column when using the Text to Columns function.
Select "Data" from the top menu bar and click on "Options." Choose "Text to Columns" and a dialog box will open up to allow you to customize the function.
Choose the method of splitting the data in the selected cell range. You can set a specific length for each new cell or select a character such as ":" or "|" as a delimiter for the data in the original cell. The data will be cut at each point where this character occurs in the cell.
Click "Finish" when you are satisfied with your choices. You will see the split data in the columns next to the original cell.
Tips & Warnings
- When setting up your worksheet or importing from an external source, design the layout to be flexible enough to allows you to expand data into new columns when necessary. Splitting a cell will place the data in the columns immediately to the right of the original cell. Any existing data in those columns will be overwritten. Insert a new column or copy any important data to another location before using the "Text to Columns" function.
- Run the text conversion on a few cells to make sure it is working correctly before running it on your entire data set.