Jun 14, 2021 By dividing cells in Excel, we suggest adding a new column, changing the column widths, and merging two cells into one. Splitting excels cells helps provide better sorting and filtering features for your data. Using the Unmerge Cells, Text to Column feature, and Flash Fill features, you will be able to split Excel cells. In this article, we. VBA Code to Split the Content of a Cell. There are many codes available to split cells in excel. In this example, we used a simple code that split the content of a cell into 3 cells. We will see the Name, Age, and Country name which are split using this code. Is there a way to split a row into multiple rows based on some value in a cell? Here's an example. Say I have one row like below. A,B,C,D1 - 5 D2 - 16 D3 - 27,E. Click the table cell that you want to split. 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. To divide a cell horizontally, in the Number of rows box, enter the number of. Click the table cell that you want to split. 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. To divide a cell horizontally, in the Number of rows box, enter the number of.
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 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.
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.
Assume that you have a list of data that contains multiple lines, and you need to separate them into a single line. How can you solve this job? In this tutorial, we are going to see how to split the multiple lines from a cell and return them into separated rows or columns in Excel. Let’s see them below!! Get an official version of MS Excel from the following link: https://www.microsoft.com/en-in/microsoft-365/excel
Jump To:
Steps to Split the multiple lines:
Divide A Cell Into Two Rows Excel Cell
You can use the below formula to extract the multiple lines.
=TRIM( MID(SUBSTITUTE( $B2, CHAR(10), REPT( ” “,LEN($B2) ) ), (C$1-1)*LEN($B2)+1, LEN($B2)))
Argument Explanations:
- TRIM – This function will help to remove the extra spaces from the text and there is no space at the start or end of the text string. Read more on the TRIM function.
- MID – In Excel, the MID function helps to extract the number (starting from the left side) or characters from the given string
- SUBSTITUTE – It will replace existing text with new text in a text string when you want to replace text based on its content, not position. Read more on the SUBSTITUTE Function.
- CHAR – This function helps to get a character by a specified number. Read more on the CHAR function.
- REPT – The Excel REPT function repeats characters a given number of times.
- LEN – In Excel, the LEN function returns the length of a given text string as the number of characters.
Practical Example:
- First, you need to enter the text string in multiple lines by clicking the
ALT+ENTER keys. It will display the input data line by line as shown below.
Split A Cell Into Multiple Rows Excel 2016
- Then apply the above-given formula to the formula bar section.
- After that press the Enter key, it will split the text and return it into separate rows.
Bottom-Line:
From this short tutorial, you can understand how to split the multiple lines from a cell and return the values into separate rows in Excel. Hope you like it. Please state your query in the below comment section. Thank you so much for visiting Geek Excel!!