How to Split a Cell in MS Excel

Techwalla may earn compensation through affiliate links in this story.
Image Credit: damircudic/E+/GettyImages

At times when you're working with Excel spreadsheets, data is organized naturally into columns that each represent a particular attribute of the information associated with a certain row. For example, one column might contain people's names, another their addresses and a third their phone numbers. Other times, you may need to split cells in Excel if they contain multiple sets of data you want to analyze independently, such as when one column contains both street addresses and ZIP codes.

Advertisement

How to Split Cells in Excel

How to split a cell in Excel depends on whether you want to do so using an Excel formula or a function in Excel's graphical user interface.

If you want to split a group of cells according to certain rules, you can use the "Convert Text to Columns" wizard in Excel for Office 365, Excel 2016 or Excel 2013.

First, select the cell or cells that you want to split. Note that when the cells are split, some of the content will overwrite columns to the right of the cells, so make sure there is room to do so without losing data. Add additional columns if you need to before splitting the cells by selecting the column you intend to split and then clicking "Home," "Insert" and then "Insert Sheet Columns."

Advertisement

When you're satisfied that you have the column space to split the cells, select the relevant cells and click the "Data" tab in the ribbon menu. Then, click "Text to Columns" within the "Data Tools" section. From there, you can use the wizard to split the cells as desired. First, select the characters you want to use as delimiters, which are characters that Excel interprets as separators between sections of the cell text that should be split into separate cells.

For example, in a phone number formatted as 555-555-1212, you could separate the area code, exchange and final section of the number using a hyphen as the delimiter. Other common delimiters are spaces, tabs, semicolons and commas.

Advertisement

Use the check box to indicate whether multiple consecutive delimiter characters should be treated as one or should be handled separately to generate multiple splits. Then, click the "Next" button.

Use the "column data format" selector to indicate whether the split columns should have formats, such as numbers or dates, that are different from the original data. Click "Finish" when you are satisfied to split the cells.

Splitting Cells With a Function

In some cases, you may want to grab certain portions of a cell to insert into other cells using an Excel formula. You can use the LEFT, RIGHT and MID functions in Excel to do this.

Advertisement

With the LEFT and RIGHT functions, you specify the text or cell that you want to extract text from and the number of characters, starting on the left or right, that you want to grab. For example, LEFT(A5, 2) grabs the leftmost two characters of cell A5, while RIGHT(B2, 7) grabs the rightmost seven characters of cell B2.

Use the MID function to specify both the starting point from the left of the cell and how many characters to grab. For example, MID(A2, 5, 10) grabs 10 characters from cell A2 starting at character 5.

Advertisement

references