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

When you import data from a database or any other sources, 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 effective methods including real-life examples.

Download Practice Workbook

How 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. We can do that using the following methods.

Dataset of how to split one cell into two in excel

1. Split Cell using Text to Columns

In the dataset, we see the name of my favorite English literary writer. Right now, we’ll split the name into first name and last name using Text to Columns.

Text to Columns is a handy feature in Excel that uses a delimiter to parse the text in one cell/column in many columns.

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.

You can proceed with the following steps.

Steps:

  • Select the whole data e.g. B4:B11
  • Pick the Text to Columns option from the Data tab
  • Choose the Delimited option.
  • Press Next

Split Cells Using Text to Columns

  • Now select the Space option
  • Press Next

Split Cells Using Text to Columns

  • Select the Text option from column data format
  • Adjust your destination if necessary and press Finish.

Split Cells Using Text to Columns

And you’ll get like the following

Split Cells Using Text to Columns

2. Split Cell with Flash Fill

Flash Fill is a special Excel tool that automatically completes values when a pattern in the data is identified. It is available from Microsoft Excel 2013 version.

It is one of those machine learning techniques for evaluation of a data pattern, pattern learning, and cell filling using that pattern.

You can split the name in the dataset into first and last name with the help of this tool.

Steps:

  • Select a blank cell e.g. C5
  • Type the first name William of B5 cell in the selected cell C5
  • Double click on the lower right corner of the cell. The whole-cell range will be automatically filled up. Here, a lot of alternatives are available e.g. you can drag the cursor to the last cell of the cell range or you can pick the Flash Fill option directly from the Data>Data Tools>Flash Fill.

Split Cells Using Flash Fill

  • Now select the Flash Fill option.

Split Cells Using Flash Fill

The tool automatically fills the first name in the whole cell range like the following

Split Cells Using Flash Fill

Similarly, you can use the feature for finding last names. In that case, you have to type the last name of the B5 cell in the D5 cell. Now follow the same process as previous. You’ll get the following output.

Split Cells Using Flash Fill

3. Split Cell Applying Formulas

i. Split Cell by Delimiter

We can separate the name into first and last name using the Excel functions with a delimiter. In our dataset, space is the delimiter that exists in the middle of the name.

Let’s have an overview of the functions that will be used here.

The LEFT function extracts text from the left of a string. The syntax of the function is

=LEFT (text, [num_chars])

There are the following arguments in the function.

text – The text from which to extract characters.

num_chars – [optional] The number of characters to extract, starting on the left side of text.

The RIGHT function extracts a given number of characters from the right side of a supplied text string. The syntax of the function is

=RIGHT (text, [num_chars])

There are the following arguments in the function.

text – The text from which to extract characters on the right.

num_chars – [optional] The number of characters to extract, starting on the right.

The Excel SEARCH function returns the location of one text string inside another

The syntax of the function is

=SEARCH (find_text, within_text, [start_num])  

The arguments of the syntax are the following

find_text – The text to find.

within_text – The text to search within.

start_num – [optional] Starting position in the text to search.

FIND function returns the position (as a number) of one text string inside another.

The syntax of the function is

=FIND (find_text, within_text, [start_num]) 

The arguments of the syntax are the following

find_text – The text to find.

within_text – The text to search within.

start_num – [optional] The starting position in the text to search.

Using the LEN function, we can get the length of a text. The syntax of the function is

 =LEN (text) 

There are the following arguments in the function.

text – The text for which to calculate length.

The SUBSTITUTE function replaces a text in a given string. The syntax of the function is

=SUBSTITUTE (text, old_text, new_text, [instance]) 

There are the following arguments in the function.

text – The text to change.

old_text – The text to replace.

new_text – The text to replace with.

instance – [optional] The instance to replace.

Right away, we are going to find the first name using the combination of LEFT and SEARCH functions.

For this, select a blank cell like C3 and type the formula =LEFT(B5,SEARCH(" ",B5)) where B5 is the cell from which we want to separate the name. And press Enter and use the Fill Handle tool for getting all the outputs of the column.

Split cells applying delimiter formula

In the same way, we can find the last name by using the following formula i.e.

=RIGHT(B5,LEN(B5)-FIND("*",SUBSTITUTE(B5," ","*",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))))) where B5 is the text that we want to split, space(“ “) is our delimiter.

Split cells applying delimiter formula

ii. Split Cell by Line Break

This method is also similar to the previous except we have to use the CHAR function here.

CHAR function returns the character specified by the code number from the character set for your dataset. Here the code means the ASCII code.

The syntax of the function is

=CHAR (number) 

There are the following arguments in the function.

number – A number between 1 and 255.

Now, we can get the first name by entering the formula in a blank cell and use the Fill Handle Tool for the other cell. The formula is =LEFT(B5,SEARCH(CHAR(10),B5,1)-1) where B5 is the text and 10 is the ASCII code for Line feed.

Note. We subtract 1 because we don’t want to extract the delimiter “space” itself.

Split cell by line break

Likewise, type the formula in a blank cell i.e. =RIGHT(B5,LEN(B5)-SEARCH(CHAR(10),B5,SEARCH(CHAR(10),B5))) where B5 is the cell we want to split and 10 is the ASCII code for Line feed. Now press Enter and use the Fill Handle Tool for the other cell in the same column.

Split cell by line break

4. Split Text+Number Pattern Cell

Sometimes, we may split a cell that contains a Text+Number pattern. In that case, we can follow the procedure. Here, we have to use the SUM function along with other functions.

SUM function returns the sum of values supplied. These values may be ranges, arrays, numbers, etc. The syntax of the function is

=SUM (number1, [number2], [number3], …) 

There are the following arguments in the function.

number1 – The first value to sum.

number2 – [optional] The second value to sum.

number3 – [optional] The third value to sum.

The easiest way to split such type pattern based cells is to separate the number first. Then find the text. Now, select a blank cell and type the formula i.e. =RIGHT(B5,SUM(LEN(B5) - LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"},"")))) where B5 is the cell that you want to split.

Now press Enter and use the Fill Handle Tool for the other cells.

Split Cell Text+Number Pattern

For finding the text, type the formula in a blank cell. The formula is

=LEFT(B5,LEN(B5)-LEN(D5)) is B5 the cell that we want to split and D5 is the number that we found from the previous formula.

Split Cell Text+Number Pattern

5. Split Cells Using Power Query

We can separate the name into first and last name also using Power Query. It is a free Microsoft Add-in to import data from various sources and then clean, transform and reshape your data if necessary.

You may proceed with the following steps.

Steps:

  • Select the whole cell range B4:B11
  • Click on From Table of Data tab
  • Press Ok

Split Cells Using Power Query

Now you are in the Power Query Editor. And select Home>Split Column>By Delimiter.

Split Cells Using Power Query

Then you’ll see the following figure. Select the Space as your delimiter and press Ok.

Split Cells Using Power Query

The analysis is completed actually. But you have to load the file in your existing working sheet. For this select Home>Close & Load>Close & Load To.

Split Cells Using Power Query

Then choose your destination and press Load

Split Cells Using Power Query

Finally, you’ll get the output like the following-

Split Cells Using Power Query

Things To Keep in Mind

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

I have discussed the most effective and handy ways. Now, choose one based on your data and requirements. And if you have any suggestions and confusion, please let me know.

Thanks for being with me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo