82 Microsoft’s official method involves using Text in Columns to divide cells in Microsoft Excel. This method replaces any text that is in the cell directly to the right of the one you are splitting. To avoid missing anything, create a new empty column next to the cell you are 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 data is dividing correctly. Click Apply and choose the data format for your new cell(s). This usually only appears on the desktop version. In most cases, you will keep the default option, which is the same as the original cell. Click Finish. This option works for simple divisions. For more complex options, you’ll want to use Text for Excel columns or functions (next section). Flash Fill helps you spread cells in Microsoft Excel by recognizing patterns and auto-fill cells based on that pattern. Start by enabling Flash Fill if you haven’t already. Go to “File -> Options -> Advanced” and go to the Editing Options area. Check “Automatically Flash Fill” and “Enable AutoComplete for Cell Values.” Press OK to save your changes. Then 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 would type “Crowder” in the cell next to “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 on the cell you just typed – “Crowder” for me. Then click Flash Fill. This fills everything in the column. Drag the lower right corner of the cell to the next one. You will see a small icon appear. Click on it and choose “Flash Fill”. Make sure you get the correct data from the next cell. In that case, drag the corner down to fill the empty column for as long as you need. Select “Flash Fill” to automatically fill the column. If you need something more robust than Text to Columns, there is another option. You will need to use the LEFT, RIGHT, MID, LEN and SEARCH functions to divide cells in Microsoft Excel. The exact formula varies based on how you’re dividing the cell. For example, if you’re splitting names, you’ll need a formula for getting the first and another for getting the last. Of course, things get more complex when you’re dealing with middle and hyphenated names. Start by creating new columns next to the cell you want to split (it doesn’t need to be directly next to it). You will need a new column for each data you want to split. For example, if you are dividing a cell into three parts, you will need three empty columns. Click on the first empty cell in the first column next to the cell you want to split. To make the example easier, I’ll split a name. The formula for getting the first name would look like (click the cell and then click the formula / function area to enter the formula): The formula looks to the left side of the characters in the cell you are dividing. (A2 is the cell you are dividing.) The SEARCH part tells the formula to start in space (use the delimiter in your cell) and go left until you reach the first character. For the last name, you will need to subtract what you originally found from the result. The formula would look like: The LEN function helps to count the correct position of the character in his cell. Like the LEFT function, the RIGHT function starts to the right of the space or delimiter. The MID function works to split data from the middle of your cell. This helps you get things like a middle name.
Table of Contents
This tip is about the how to Split Text or Data in MS Excel. So read this free guide, How to Split Text or Data in MS Excel. If you have query related to same article you may contact us.
How to Split Text or Data in MS Excel – Guide
On your journey in Excel, you will run into situations where you need to work with imported data. In these cases, it is exceptionally normal to find information cells that are not in the desired format. For example, I have an Excel spreadsheet where a section of cells displays the first and last names of customers together. In any case, I really want to split the cells into two separate columns for first and last name.
Using text for columns
Flash fill
Excel functions
= LEFT (A2, SEARCH (“”, A2.1))
= RIGHT (A2, LEN (A2) -SEARCH (”“, A2,1))
Final note
I hope you like the guide How to Split Text or Data in MS Excel. In case if you have any query regards this article you may ask us. Also, please share your love by sharing this article with your friends.