Back to Blog

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! 

1 excel integrations coupler

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.

2 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
3 delimiter or 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.
4 set delimiter
  • Select the date format and destination cell. If you want to keep the source value, choose a different cell as a destination.
5 date format destination cell

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.

6 cell split 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
7 pablo picasso full name

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

8 pablo picasso full name split

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. 

9 split by character

There you go!

10 split by character result

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.

11 split by line break

Here is the result:

12 split by line break 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.

merge and center button

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

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. 
split text to columns
  • Copy the resulting cells and paste them using Paste special => Transpose.
paste 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)
13 formula split first row

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

2. Second

= RIGHT(cell, LEN(cell) - FIND(" ",cell))
14 formula split second row

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:

15 formula split two rows

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) )
16 formula split two rows

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")
17 split multiple rows

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]")
18 formula extract specific word

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.
19 data from table
  • Select the cell with your data and click “OK“.
20 select cell as table
  • After that, the Power Query Editor will open. Then select Split Column => By Delimiter.
21 power query split column by delimiter
  • Select your delimiter, click “Advanced options“, choose Split into Rows, and select “None” as a Quote Character.
22 split configuration
  • 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.
23 close and load

There you go!

24 power query split data

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

25 power query split data dynamic

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:

26 diagonal crossing

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…
27 formal cells
  • In the open dialog window, go to the Border tab and click the diagonal border. 
28 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.
29 diagonal borderline
  • 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. 
30 select one word
  • In the dialog box, check the Superscript font effect and click OK
31 superscript
  • Here is how it looks now
32 superscript one value
  • Now, do the same with the second value using the Subscript option.
33 subscript
  • And there you go!
26 diagonal crossing

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!

  • Zakhar Yung

    A content manager at Coupler.io whose key responsibility is to ensure that the readers love our content on the blog. With 5 years of experience as a wordsmith in SaaS, I know how to make texts resonate with readers' queries✍🏼

Back to Blog

Comments are closed.

Focus on your business
goals while we take care of your data!

Try Coupler.io