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.


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.

How to Split One Cell into Two in Excel


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.

split One Cell into Two Using Text to Columns Feature in Excel

  • 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.

Apply Flash Fill Feature in Excel to Separate Cell

  • 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)

Use Delimiter for Splitting One Cell into Two in Excel

🔎 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))

Use Delimiter for Splitting One Cell into Two in Excel

🔎 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)

Insertion of Line Break o Split One Cell into Two in Excel

🔎 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.

Sample Dataset to Combine RIGHT, SUM, LEN, and SUBSTITUTE Functions to Split Cell

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"},""))))

Combination of RIGHT, SUM, LEN, and SUBSTITUTE Functions to Split Cell in Excel

  • 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.

Break One Cell into Two Through Excel Power Query in Excel

  • 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.

Break One Cell into Two Through Excel Power Query in Excel

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

 

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo