How to Split Cells in Excel – All Options Explained

You may need to split a value in your cell into two or more columns by space, character, or another delimiter. This can be done easily with a few clicks. On the other hand, some users need to split cells into rows. Although Excel does not have this functionality out of the box, you can use a few functions to do the job. Read on to learn all the options available for splitting cells in Excel.

How do I split cells in Excel?

You can split cells in Excel with the following options:

• Using a native functionality
• Using a combination of Excel functions
• Power Query
• Visual Basic for Applications (VBA)

For this article, we have decided to group the use cases not by option, but by the expected outcome. So, we’ll cover two epic tasks: split cells to columns and split cells to rows. Each of these tasks will include the options to split by different delimiters and other specific requirements. 

For the examples below, we imported data from different sources such as Airtable, Google Sheets, and BigQuery with Coupler.io. It’s a powerful solution for getting data to Excel from multiple apps and web APIs without coding. The main benefit is that you can synchronize your workbook with your data source on a custom schedule! 

Check out the Microsoft Excel integrations available.

Let’s check out each option so you can choose the one that works best for your task.

How to split one cell into two cells in Excel

We’ll warm up with a simple example of splitting a full name, Margeaux Kuhnke, into first and last name cells. Go to the Data ribbon and click Text to Columns.

A wizard will open, where you’ll need to perform three steps:

• Choose the way to split your data: by a delimiter or by a fixed width

• Set delimiters or field width depending on which splitting method you chose before. In our example, we need to split a cell by space as a delimiter.

• Select the date format and destination cell. If you want to keep the source value, choose a different cell as a destination.

Once you click “Finish“, your cell will be split into the number of cells depending on the number of delimiters in your source value. In our example, we had one delimiter, so the cell was split into two cells.

Split text in Excel cell into multiple cells by space 

If a full name in a cell consists of three or more names, the number of split cells will be greater. For example, let’s split Pablo Picasso’s full name using space as a delimiter:

Pablo Diego José Francisco de Paula Juan Nepomuceno María de los Remedios Cipriano de la Santísima Trinidad Ruiz y Picasso

The outcome is that we’ve split the text in the Excel cell into multiple cells (20!) by spaces!

Excel split cells by character

Splitting cells by character may be useful if you need to separate, for example, username and domain name in email addresses. Let’s say we have a fictional user with the following email address:

elon_musk_2021@coupler.io

In this case, the delimiter is a character: @. So, we need to specify this. 

There you go!

Excel split data in cell by line break

What if your cell contains a few lines like an address, and you want each line in its own cell?

8054 Old Shore Parkway
Houston
United States

To split this string, you need to specify the line break as a delimiter. To do this, choose Other as a delimiter and press Ctrl + J.

Here is the result:

How to split merged cells in Excel

Splitting merged cells is a slightly different concept, since it does not affect the content of the cells. You can do this easily using the Merge & Center button.

Select the merged cells, open the drop-down options and click Unmerge Cells.

Split cells into rows in Excel

Unfortunately, there is no single button or function to split a cell into rows or to split cells vertically in Excel. Nevertheless, you can do this with the help of Excel functions or Power Query. But first, check out this easy method for splitting cells into rows.

Split Excel cell into two rows easily

You’ll need to take two steps:

• Split your cell using the Text to Columns button as we explained before. 

• Copy the resulting cells and paste them using Paste special => Transpose.

This is the simplest way to split cells to rows in Excel, but it’s manual. Let’s discover some options to automate the flow.

Split cells vertically in Excel with formulas

Let’s take the previous example of splitting a full name: Margeaux Kuhnke.

We’ll need two formulas for each row:

1. First

= LEFT(cell, FIND(" ",cell) - 1)

This formula extracts a string before the specified delimiter: FIND(" ",cell).

2. Second

= RIGHT(cell, LEN(cell) - FIND(" ",cell))

This formula extracts a string after the specified delimiter.

If you have two or more delimiters, both formulas will only work with the first delimiter, like this:

If for some reason you need to split a cell by a second or third delimiter, you need to update the FIND formula, keeping the following logic in mind:

=FIND("delimiter",cell,starting_position)

• starting_position – specify the starting position (character) to skip the unnecessary delimiters.

In our example, to split the cell by the second delimiter, the formulas will look like this:

=LEFT(A1,FIND(" ",A1,10)-1)

=RIGHT(A1, LEN(A1) - FIND( " ",A1,10) )

Split cells in Excel into multiple rows

Unfortunately, these formulas do not apply to the “Pablo Picasso” case, where the number of delimiters is 19! For such cases, the following formula works better:

=FILTERXML("<t><s>" &SUBSTITUTE($cell," ", "</s><s>") & "</s></t>", "//s")

This converts cell contents into an XML string and then extracts data from it. This formula should be executed as an array formula; that is, you need to select the cell to split into and press Ctrl+Shift+Enter. Here is how it works with the Pablo Picasso’s full name:

=FILTERXML("<t><s>" &SUBSTITUTE($A$1," ", "</s><s>") & "</s></t>", "//s")

You can also use this formula to extract specific pieces of your string by specifying them as follows:

=FILTERXML("<t><s>" &SUBSTITUTE($cell," ", "</s><s>") & "</s></t>", "//s"[number_of_piece])

For example, to extract the third name from the Picasso’s full name, we update our formula as follows:

=FILTERXML("<t><s>" &SUBSTITUTE($A$1," ", "</s><s>") & "</s></t>", "//s[3]")

How to split cells to rows or columns in Excel dynamically without formulas

If you don’t want to mess with formulas for your splitting tasks, but you want to have a dynamic approach, check out the Power Query method. Let’s check it out for our Pablo Picasso example.

• Go to Data => From Table.

• Select the cell with your data and click “OK“.

• After that, the Power Query Editor will open. Then select Split Column => By Delimiter.

• Select your delimiter, click “Advanced options“, choose Split into Rows, and select “None” as a Quote Character.

• The data will be split into rows. Now you need to save and load it. For this, click:
  • “Close & Load” to load data in a new worksheet.
  • “Close & Load To…” to specify a custom destination for your data.

There you go!

Now, every value added to the A column will be split into rows by space. Just click refresh to see the split values.

See, with Power Query you can also split cells into columns by different delimiters.

Bonus: How to split cells diagonally in Excel

By diagonal split, users usually mean crossing a cell with a diagonal border like this:

This does not split a cell into columns or rows, but makes a visual split of two values in the cell. Here is how you can do this.

• Right-click on the cell you want to split diagonally and select Format Cells…

• In the open dialog window, go to the Border tab and click the diagonal border. 

• Click OK and you will see a line splitting the selected cell diagonally. But this is not quite how we want it to look.

• To fix this, we need to assign the Superscript font effect to one value in the cell, and Subscript to the other value. Select one word – in our case, this is “Mithun“. Then go to the Home tab and click the Font Settings – a small arrow at the bottom-right corner of the Font section. 

• In the dialog box, check the Superscript font effect and click OK. 

• Here is how it looks now

• Now, do the same with the second value using the Subscript option.

• And there you go!

Are there other options to split cells in Excel? 

In Excel, there is no dedicated function for splitting data, like SPLIT in Google Sheets. However, using Excel VBA, you can build a custom VBA function to solve your specific splitting tasks. For example, here is an example of how to split Excel sheets into multiple worksheets using VBA. This requires knowledge of Microsoft Visual Basic for Applications. 

However, for most cases, the options previously described will do the job. Good luck with splitting your data!

Back to Blog