Using Text to Columns
The official Microsoft method involves using Text to Columns to split cells in Microsoft Excel. This method overwrites any text that’s in the cell directly to the right of the one you’re splitting. To avoid losing anything, create a new empty column beside the cell you’re splitting. Select the cell you want to split. You can select multiple cells at the same time if you wish. Select the Data tab and choose the “Text to Columns” option. Choose “Delimited” if prompted. Select the type of delimiter(s) your cell uses. Use the preview pane to see if your cell’s data is splitting correctly. Click Apply and choose the data format for your new cell(s). This usually only appears in the desktop version. In most cases, you’ll stick with the default option, which is the same as your original cell. Click Finish.
Flash Fill
This option works for simple splits. For more complex options, you’ll want to use Text to Columns or Excel functions (next section). Flash Fill helps you spilt cells in Microsoft Excel by recognizing patterns and auto-filling cells based on that pattern. Start by enabling Flash Fill if you haven’t already. Go to “File -> Options -> Advanced” and scroll to the Editing Options area. Check “Automatically Flash Fill” and “Enable AutoComplete for Cell Values.” Press OK to save your changes. Next, create a new column directly beside the cell you want to split. It must be directly beside it, or it won’t work. In the new column, type the part of the cell you want to split. For example, if I’m splitting my name, I’d type “Crowder” in the cell beside “Crystal Crowder.” Depending on the version of Microsoft Excel you have, there are two methods for the next step. Open the Data tab and see if you have a Flash Fill option. If so, click the cell you just typed in – “Crowder” for me. Then, click Flash Fill. This fills in everything in the column. Drag the bottom-right corner of the cell down to the next one. You’ll see a small icon appear. Click it and choose “Flash Fill.” Ensure it grabs the correct data from the next cell. If so, drag the corner down to fill the empty column for as long as you need it. Select “Flash Fill” to auto-fill the column.
Excel Functions
If you need something more robust than Text to Columns, there is another option. You’ll need to use the LEFT, RIGHT, MID, LEN, and SEARCH functions to split cells in Microsoft Excel. The exact formula will vary based on how you’re splitting the cell. For instance, if you’re splitting names, you’ll need one formula to grab the first and another to grab the last. Of course, things get more complex when you’re dealing with middle and hyphenated names. Start by creating new columns beside the cell you want to split (doesn’t have to be directly beside). You’ll need a new column for each piece of data you want to split. For example, if you’re splitting a cell into three parts, you’ll need three empty columns. Click in the first empty cell in your first column beside the cell you want to split. To make the example easier, I’ll split a name. The formula to grab the first name would look like (click the cell and then click the formula/function area to enter the formula): The formula looks at the left side of characters in the cell you’re splitting. (A2 is the cell you’re splitting.) The SEARCH portion tells the formula to start at the space (use the delimiter in your cell) and go left until it reaches the first character. For the last name, you’ll need to subtract what you originally found from the result. The formula would look like: The LEN function helps count the correct position of the character in your cell. Just like the LEFT function, the RIGHT function starts from the right of the space or delimiter. The MID function works to split data from the middle of your cell. This helps you grab things like a middle name. Since the formula can vary so much, it’s a good idea to look at the variety of scenarios from Microsoft to see how to split different formats and how to vary the formula as needed. If the formula stays the same throughout the column, drag the bottom-right corner to fill the column. What’s your favorite way to split cells in Microsoft Excel? If you need to merge cells instead, learn how to merge both cells and columns easily.
