Microsoft Excelis an incredibly powerful application that can tackle everything from simple tables to complex financial spreadsheets, and you can evenpassword-protect an Excel fileto keep your data secure. It can take years to master Excel’s functionality, and most of us don’t have that much time.
Difficulty
What You Need
Microsoft Excel
Sometimes, you have something very simple that you need to accomplish. Splitting a cell into multiple cells is one such task, and it can be helpful when you need to clean up a spreadsheet full of data. In this guide, we’ll show you how to split cells in Microsoft Excel in a few easy steps.
How to split a cell into multiple rows
Splitting a cell into multiple rows can be helpful if you need to break out data such as names and titles. In the small example spreadsheet, that’s exactly what we have.
Step 1:To split these cells into their separate parts, select them and then click onText to Columnsin the Data menu.
Step 2:In the dialog that pops up, you’ll need to choose how to split your data. You can choose delimited, which splits the data based on characters embedded in it such as spaces and commas, or you can choose fixed width where your data is uniform. Here, we’re usingDelimited Data, so select that option and clickNext. Here, we want to chooseSpace, because that’s what separates each data element in our list. Excel will show you how your data will look in a preview at the bottom of the window.
You can choose to treat consecutive delimiters as one (such as having multiple spaces between each data element) or not, and whether to identify text via a qualifier.
Step 3:ClickNext. In this window, you can choose the data format, the beginning destination cell (defaulted to the current cell), and someAdvancedoptions such as how to recognize numeric data.
Note that the columns next to the destination cell must be empty or you’ll be presented with an option to overwrite any existing data. It’s best to create empty columns that equal the number of columns you’ll need to display your split data.
Step 4:ClickFinish. Your data will now be split into multiple columns.
How to split a column in Microsoft Excel
The process for splitting an entire column in Microsoft Excel is exactly the same as for splitting specific cells.
Step 1:You first select the column, and then click onText to Columnsin theDatamenu.
Step 2:Next, simply follow the same steps as used in splitting a cell.
How to use a formula to split a cell in Microsoft Excel
When you have just two data element in cells and need to split them, there are two relatively easy formulas you may use that takes advantage of a couple of text function in Microsoft Excel. If you have more than two data elements, some complicated programming is required which is outside the scope of this guide.
Step 1:First, you’ll use the formula=LEFT(A2,SEARCH(” “,A2)-1)in cell B2 in this example to pull out the first name. Replace the cell reference as required in your spreadsheet.
TheLEFTtext function will extract a specific number of characters from the left part of a cell. TheSEARCHfunction will find the space that separates the data elements and tell theLEFTfunction how many characters to extract.
Step 2:Copy the formula to the rest of the cells, making sure to change the cell reference number accordingly. you’re able to drag and copy the formula.
Step 3:Next, you’ll use the formula=RIGHT(A2,LEN(A2)-SEARCH(" “,A2))to extract the right portion of the cell. Again, make sure the cell references are correct.
Step 4:Again, copy the formula to the rest of the cells, making sure to change the cell reference number accordingly. you’re able to drag and copy the formula.
Step 5:These formulas are dynamic, meaning if you change a value in a cell then it will reflect in the split cells. Let’s change Bob to Jose in the first cell.
It’s as easy as that. And now that you’ve successfully split your cells, you can manipulate your data in more fine-tuned ways.
