FIND(” “,B5): The FIND function here again looks for the space character in Cell B5 and returns the position which is ‘7’.LEN(B5): The LEN function counts the number of total characters found in Cell B5 and thereby returns ‘14’.➤ Select Cell C5 and type the following formula:Īnd after pressing Enter, we’ll get another part of the name in the corresponding output cell. Let’s go through the following steps now to extract first and last names separately with formulas. The FIND function looks for a specific character or text in a cell and returns the starting position of that character or text. The LEFT and RIGHT functions pull out the specified numbers of characters from the left and right of a text respectively. We can split cells By using LEFT, RIGHT, and FIND functions too. Split a single cell in half in Excel (diagonally & horizontally).
#Excel split cells how to
So let’s find out how we can split names from the Text column by using this application. It saves a lot of time while working with a large data table. Apply Flash Fill Option to Divide Cellsįlash Fill tool is another interesting feature in MS Excel to return output in a range by following a specific pattern in a dataset. You have to simply edit two headers with the texts ‘Name’ and ‘Age’.ģ. You can also keep the headers separated by not choosing the option to merge them. So, now the Name & Age column has been divided into two parts. ➤ From the Alignment group, click on the Merge & Center command. ➤ Now select the Name & Age header along with the blank header of the newly inserted column. ➤ Right-click your mouse and launch the Context Menu.Īn additional column has been added to the right of the Name & Age column. This method is useful when you have to work with a large table but suddenly you’ll need to split any column inside the data table. In the following table, we’ll divide the Name & Age column into two columns while keeping the header name unchanged.
Now we’ll see how an empty column under a header can be split into two parts. Insert and Merge Columns to Split Cells in Excel ➤ Now select the destination cell where you want to see the split texts.Īs shown in the screenshot below, you’ll find all names split into two parts in Cell 1 and Cell 2 columns.Ģ. ➤ From the Delimiters options, put a checkmark on Space only. ➤ In the wizard as shown below, click Next. ➤ From the Data ribbon, select the Text to Columns tool from the Data Tools group. ➤ First, select the texts that you want to split into multiple cells. In our first method, we’ll use the Text to Column feature which is essentially used to split a text into different parts that were once delimited by symbols or spaces. The first part will be displayed in Cell 1 column and another part in Cell 2 column. What we’ll do here is split each name into two parts.
In the following table, there are some random names lying in the Text column. Split Cells in Excel with Text to Column Feature