How to Split One Cell into Two in Excel (5 Useful Methods)

When you import data from a database or any other source, you have to split cell one into two or more in such situations. In this tutorial, I’m going to discuss how to split one cell into two in Excel using the following 5 effective methods, including real-life examples. Hence, go through it carefully in order to learn more.

How to Split One Cell into Two in Excel: 5 Useful Methods

In this part, I will show you five useful methods to split one cell into two in Excel. Here, we have a dataset where column B mainly consists of full names. Now, we have to split the cell of column B into two columns, e.g., first name and last name. Moreover, I should not mention that I have used the Microsoft Excel 365 version for this article; you can use any other version according to your convenience. For the purpose of demonstration, I have used the following sample dataset.

1. Split One Cell into Two Using Text to Columns Feature

In the dataset, we see the names of some English literary writers. Right now, we’ll split the name into first and last names using the Text to Columns feature. Moreover, the Text to Columns feature is a handy feature in Excel that uses a delimiter to parse the text in one cell/column into many columns.

In general, a delimiter is a type of character (e.g., comma, space, semicolon, etc.) that separates text strings or other data streams. In our dataset, space is the delimiter. However, you can proceed with the following steps.

Steps:

• First, select the whole data e.g. B4:B11.
• Then, pick the Text to Columns option from the Data tab.

• Next, choose the Delimited option and press Next.

• Now select the Space option and press Next.

• After that, select the Text option from the Column Data Format and adjust your Destination if necessary.
• Now, press Finish.

• Lastly, you will get your final result.

2. Apply Flash Fill Feature in Excel to Separate Cell

Usually, Flash Fill is a special Excel tool that automatically completes values when a pattern in the data is identified. It is available in Microsoft Excel 2013 and later versions. Moreover, it is one of those machine learning techniques for the evaluation of a data pattern, pattern learning, and cell filling using that pattern. However, you can split the names in the dataset into first and last names with the help of this tool.

Steps:

• Initially, select a blank cell C5.
• Secondly, type the first name William of the B5 cell in the selected cell C5.
• Thirdly, use the AutoFill tool for the entire column.

• Now, select the Flash Fill option to get your desired output.

• Similarly, you can get the same output for the last names as in the image below.

Read More: How to Split a Cell into Two Rows in Excel

3. Insert Formulas for Splitting One Cell into Two in Excel

Furthermore, you can insert formulas for splitting one cell into two in Excel. It is quite easy and handy to operate. In this part, I will show two different ways to split one cell into two.

i. Use Delimiter

We can separate the names into first and last names using the Excel functions with a delimiter. In our dataset, “space” is the delimiter that exists in the middle of the name. Also, we can split cells by using the LEFT, RIGHT, and FIND functions. However, go through the steps below.

Steps:

• Firstly, select cell C5 and type the following formula.

• Secondly, press the Enter key and use the AutoFill tool to the entire column.

• Thirdly, write the following formula in cell D5.

• Finally, use the AutoFill tool in order to get the final output.

Read More: How to Split a Single Cell in Half in Excel

ii. Insert Line Break

Fortunately, this method is similar to the previous one, except we have to use the CHAR function. Usually, the CHAR function returns the character specified by the code number from the character set for your dataset. Here, the code means the ASCII code. Moreover, we have used the LEFT, RIGHT, and SEARCH functions. However, follow the steps below.

Steps:

• First, write down the formula below in cell C5.

• Then, hit the Enter key and use the AutoFill tool to the whole column.

• After that, write the formula below in cell D5.

• Finally, press the Enter key and utilize the AutoFill tool to get the final output.

Read More: Excel Formula to Split String by Comma

4. Combine RIGHT, SUM, LEN, and SUBSTITUTE Functions to Split Cell

Sometimes, we may split a cell that contains a text and number pattern. In that case, we can follow the below procedure. Here, we have to use the SUM function along with other functions used in the previous method. In general, the SUM function returns the sum of the values supplied. These values may be ranges, arrays, numbers, etc. In addition to this, I have combined the RIGHT, LEN, and SUBSTITUTE functions. For the purpose of demonstration, I have changed the dataset slightly.

Steps:

• First, select cell D5 and write down the formula below.

• Then, press Enter and apply the AutoFill tool.

• Again, write the following formula in cell C5.

• In the end, hit the Enter key and apply the AutoFill tool in order to get the desired output.

5. Break One Cell into Two Through Excel Power Query

Last but not least, Power Query is another excellent feature in MS Excel for splitting or dividing cells into multiple columns. Let’s find out how we can use Power Query to meet our objectives. However, we’ll work with a similar dataset and split the surnames into first and last parts. Therefore, follow the steps below to split one cell into two in Excel.

Steps:

• Initially, select the surnames from the entire column, including the header.
• Secondly, go to the Data tab and click on From Table/Range.
• Thirdly, press OK.

• Fourthly, you are in the Power Query Editor and select

Home>Split Column>By Delimiter.

• Then, select Space as your delimiter and press OK.

• After that, select

Home>Close & Load>Close & Load To.

• Then choose your destination from the Import Data dialog box and press OK.

• Finally, the output will appear as shown in the below figure.

Read More: How to Split Cell by Delimiter Using Excel Formula

Things To Keep in Mind

• First, be careful about the formula when you input it in the formula bar.
• Besides, be cautious about the file name, file location, and the extension of the Excel file.

Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.

Conclusion

These are all the steps you can follow to split one cell into two in Excel. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.