Split a Cell | Text to Columns | Flash Fill | Formulas to Split Cells
To split a cell in Excel, add a new column, change the column widths and merge cells. To split the contents of a cell into multiple cells, use the Text to Columns wizard, flash fill or formulas.
Split text strings into multiple rows/columns by space/comma/delimiter with Kutools for Excel 1. Select the range you will split text strings (in our case we select the range A1:A9 ), and click Kutools Text. In the opening Split Cells dialog box, please check the Split to Rows option. Excel Details: Split A Cell Horizontally Excel.Details: Under Table Tools, on the Layout tab, in the Merge group, click Split Cells, and then do one or more of the following: To divide a cell vertically, in the Number of columns box, enter the number of new cells that you want.
Split a Cell
Use the following trick to 'split' a cell in Excel.
1. For example, task B starts at 13:00 and requires 2 hours to complete.
Suppose task B starts at 13:30. We would like to split cell B3 and color the right half.
2. Select column C.
3. Right click, and then click Insert.
Result:
4. The default width of a column is 64 pixels. Change the width of column B and C to 32 pixels.
5. Select cell B1 and cell C1.
6. On the Home tab, in the Alignment group, click the down arrow next to Merge & Center and click Merge Cells.
Result:
7. Repeat steps 5-6 for cell B2 and cell C2 (and cell B4 and cell C4).
8. Change the background color of cell B3 to No Fill.
Result:
Note: suppose task A ends at 15:30. Use the trick explained above to 'split' cell E2. Download the Excel file and give it a try.
Text to Columns
To split the contents of a cell into multiple cells, use the Text to Columns wizard. For example, let's split full names into last and first names.
Excel Split Cells Lines
1. Select the range with full names.
2. On the Data tab, in the Data Tools group, click Text to Columns.
The following dialog box appears.
3. Choose Delimited and click Next.
4. Clear all the check boxes under Delimiters except for the Comma and Space check box.
5. Click Finish.
Result:
Note: this example has commas and spaces as delimiters. You may have other delimiters in your data. Experiment by checking and unchecking the different check boxes. You get a live preview of how your data will be separated.
Flash Fill
Do you like Magic? Instead of using the Text to Columns wizard, use flash fill to quickly split data into multiple columns.
1. First, split the contents of one cell into multiple cells.
2. Select cell B1 and press CTRL + E (flash fill shortcut).
3. Select cell C1 and press CTRL + E.
4. Select cell D1 and press CTRL + E.
Note: flash fill in Excel only works when it recognizes a pattern. Download the Excel file and give it a try. Visit our page about Flash Fill to learn more about this great Excel tool.
Formulas to Split Cells
One drawback when using these tools is that the output will not automatically update when the source data changes. Create formulas to overcome this limitation. Let's split full names into first and last names.
1. The formula below returns the first name.
2. The formula below returns the last name.
3. Select the range B2:C2 and drag it down.
Note: visit our page about separating strings to understand the logic behind these formulas.
You've just found a new list of leads with the addresses in a single column, and you want to split them up so you can use one or more of its components as filters.
1. The first thing that you need to do is to select the column where the address info is. Here are two ways to do it.
Mouse and Keyboard: Click the letter above the column where the address info is, and it'll select the entire column.
Keyboard Shortcut: Select any cell from the column that has the address info. Then press and hold Ctrl, and hit Space.
2. Once selected, go to the Data tab.
Mouse and Keyboard:Go into the ribbon at the top of the screen and select Data, and then choose Text to Columns.
Keyboard Shortcut:Press and hold Alt, and then press A. Let go of Alt, then press E twice.
3. This will bring up the Convert Text to Columns Wizard. On the first page, make sure you select Delimited and then press Next.
4. If the addresses in your file are separated by commas such as '1 Union Street, San Francisco, CA 94111', you should select Comma as the Delimiter and then press Next.
5. This next page will show you what your list of addresses will look like. You can press Finish to see the result of your split out addresses.
6. The final step is naming the headers such as 'Address, City, State, Zip Code' as you have multiple columns where before you just had one.
Excel Split Cells Vertically
NOTE: Once you've split the column, you will need to choose Multiple Columns when you upload it in the app. Also, remember to create new columns for the filters that you need. For example, you have the 'State' column that will be used for geolocating your account on the map. Create a duplicate, and label it as 'Filter by State' just to differentiate them according to how they will be used in the app.