Md. Abdul Kader
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.
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
5 Useful Methods to Split One Cell into Two in Excel
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.
Read More: How to Split Cells in Excel (5 Easy Tricks)
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: VBA to Split String into Multiple Columns in Excel (2 Ways)
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 name 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.
=LEFT(B5,FIND(" ",B5)-1)
🔎 How Does the Formula Work?
- FIND(” “,B5): The FIND function looks for the space character (“ “) in Cell B5 and returns the position of that character which is ‘8’.
- FIND(” “,B5)-1: After subtracting 1 from the previous result, the new return value here is ‘7’.
- LEFT(B5,FIND(” “,B5)-1): Finally, the LEFT function extracts the 1st 6 characters from the text in Cell B5, which is ‘William’.
- Secondly, press the Enter key and use the AutoFill tool to the entire column.
- Thirdly, write the following formula in cell D5.
=RIGHT(B5,LEN(B5)-FIND(" ",B5))
🔎 How Does the Formula Work?
- LEN(B5): The LEN function counts the number of total characters found in Cell B5 and thereby returns ‘18’.
- FIND(” “,B5): The FIND function here again looks for the space character in Cell B5 and returns the position which is ‘7’.
- LEN(B5)-FIND(” “,B5): This part of the entire formula returns ‘11’ which is the subtraction between the previous two outputs.
- RIGHT(B5,LEN(B5)-FIND(” “,B5)): Finally, the RIGHT function pulls out the last 11 characters from the text in Cell B5 and that is ‘Shakespeare’.
- Finally, use the AutoFill tool in order to get the final output.
➥ Read More: Excel Split Cell by Delimiter Formula
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.
=LEFT(B5,SEARCH(CHAR(10),B5,1)-1)
🔎 How Does the Formula Work?
- =SEARCH(CHAR(10),B5,1): This looks for the space character (“ “) in Cell B5 and returns ‘9’.
- =LEFT(B5,SEARCH(CHAR(10),B5,1)-1): Finally, the LEFT function extracts the initial characters from the text in Cell B5 which is ‘William’.
- Then, hit the Enter key and use the AutoFill tool to the whole column.
- After that, write the formula below in cell D5.
=RIGHT(B5,LEN(B5)-SEARCH(CHAR(10),B5,SEARCH(CHAR(10),B5)))
🔎 How Does the Formula Work?
- =SEARCH(CHAR(10),B5): This looks for the space character (“ “) in Cell B5 and returns ‘9’.
- SEARCH(CHAR(10),B5,SEARCH(CHAR(10),B5)): also returns ‘9’.
- =RIGHT(B5,LEN(B5)-SEARCH(CHAR(10),B5,SEARCH(CHAR(10),B5))the ): Finally, RIGHT function extracts the last characters from the text in Cell B5 which is ‘Shakespeare’.
- Finally, press Enter key and utilize the AutoFill tool to get the final output.
Read More: Excel Formula to Split: 8 Examples
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.
=RIGHT(B5,SUM(LEN(B5) - LEN(SUBSTITUTE(B5,{"0","1","2","3","4","5","6","7","8","9"},""))))
- Then, press Enter and apply the AutoFill tool.
- Again, write the following formula in cell C5.
=LEFT(B5,LEN(B5)-LEN(D5))
- 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.
- 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 a Cell into Two Rows in Excel (3 ways)
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 also the extension of the excel file.
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.
For more information like this, visit Exceldemy.com.
Further Readings
- How to split a single cell in half in Excel (diagonally & horizontally)
- Excel Formula to Split String by Comma (5 Examples)
- Excel VBA: Split String by Character (6 Useful Examples)
- How to Make Two Lines in One Cell in Excel (4 Methods)
- Excel VBA: Split String into Cells (4 Useful Applications)
- Excel VBA: Split String by Number of Characters (2 Easy Methods)
