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.

**Watch Video – Split One Cell into Two in Excel**

**Table of Contents**Expand

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

`=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: **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**.

`=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, the**RIGHT**function extracts the last characters from the text in cell**B5**which is**‘Shakespeare’**.

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

`=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/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.