In Excel, there are various reasons why you might need to split cell data. For example, the raw data may contain multiple pieces of information lumped into one cell, such as full names or addresses. Splitting these cells allows you to separate different types of information, making the data easier to clean and analyze. This article will serve as your comprehensive guide, showing different ways to split cells into rows or columns based on specific separators.

Split cells in Excel into multiple columns

• With Text to Column wizard
• Easily with Kutools
• With Flash Fill (Excel 2013 and later)
• With formulas

Split cells in Excel into multiple rows

• With TEXTSPLIT function (Excel 365)
• Easily with Kutools
• With VBA code
• With Power Query (Excel 2016 and later)

Video

Split cells in Excel into multiple columns

As shown in the following screenshot, suppose you have a list of full names, and you want to split each full name into separate first and last names and place the split data in separate columns. This section will demonstrate four ways to help you accomplish this task.

Split cells into multiple columns with Text to Column wizard

To split cells into multiple columns based on a specific separator, one commonly used method is the Text to Column wizard in Excel. Here, I will show you step-by-step how to use this wizard to achieve the desired result.

Step 1: Select the cells you wish to split and open the Text to Columns wizard

In this case, I select the range A2:A8, which contains full names. Then go to the Data tab, click Text to Columns to open the Text to Columns wizard.

Step 2: Configure the steps one by one in the wizard

1. In the Step 1 of 3 wizard, select the Delimited option and then click the Next button.
   

   

2. In the Step 2 of 3 wizard, select the delimiters for your data and then click the Next button to continue.
   
   In this case, since I need to split full names into first and last names based on spaces, I only select the Space checkbox in the Delimiters section.

   

   Notes:

   • If the delimiter you need is not shown in this section, you can select the Other checkbox and enter your own delimiter into the textbox.
   • To split cells by line break, you can select the Other checkbox and press Ctrl + J keys together.
     
     
3. In the last wizard, you need to configure as follows:
   
   1) In the Destination box, select a cell to place the split data. Here I choose cell C2.

   2) Click the Finish button.

   

Result

Full names in selected cells are separated into first and last names and located in different columns.

Conveniently split cells into multiple columns using Kutools

As you can see, the Text to Columns wizard requires multiple steps to complete the task. If you need a simpler method, the Split Cells feature of Kutools for Excel is highly recommended. With this feature, you can conveniently split cells into multiple columns or rows based on a specific delimiter, by completing the settings in a single dialog box.

After installing Kutools for Excel, select Kutools > Merge & Split > Split Cells to open the Split Cells dialog box.

1. Select the range of cells containing the text you wish to split.
2. Select the Split to Columns option.
3. Select Space (or any delimiter you need) and click OK.
   
   
4. Select a destination cell and click OK to get all split data.
   
   

Split cells into multiple columns with Flash Fill

Now let’s move on to the third method, known as Flash Fill. Introduced in Excel 2013, Flash Fill designed to automatically fill your data when it senses a pattern. In this section, I will demonstrate how to use the Flash Fill feature to separate first and last names from full names in a single column.

Step 1: Manually enter the first split data in the cell adjacent to the original column

In this case, I am going to split the full names in column A into separate first and last names. The first full name is in cell A2, so I select the cell B2 adjacent to it and type the first name. See screenshot:

Step 2: Apply the Flash Fill to automatically fill all first names

Start typing the second first name into the cell below B2 (which is B3), then Excel will recognize the pattern and generate a preview of the rest of the first names, and you need to press Enter to accept the preview.

Now all first names of full names in column A are separated in column B.

Step 3: Get last names of full names in another column

You need to repeat the above Steps 1 and 2 to split the last names from the full names in Column A into the column next to the first name column.

Result

Split cells into multiple columns with formulas

The above methods are not dynamic, which means if the source data changes, then we need to rerun the same process over again. Take the same example as above, to split the full names listing in Column A into separate first and last names and have the split data update automatically with any changes in the source data, please try one of the following formulas

Use TEXT functions to split cells into columns by certain delimiter

The formulas provided in this section are available in all Excel versions. To apply the formulas, do as follows.

Step 1: Extract the text before the first delimiter (first names in this case)

1. Select a cell (C2 in this case) to output the first name, enter the following formula and press Enter to get the first name in A2.
   

   =LEFT(A2,SEARCH(" ",A2)-1)

   
2. Select this result cell and drag its AutoFill Handle down to get the rest of the first names.
   
   

Step 2: Extract the text after the first delimiter (last names in this case)

1. Select a cell (D2 in this case) to output the last name, enter the following formula and press Enter to get the last name in A2.
   

   =RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

   
2. Select this result cell and drag its AutoFill Handle down to get the rest of the last names.
   
   

Use the TEXTSPLIT function to split cells into columns by specific separator

If you are using Excel for Microsoft 365, the TEXTSPLIT function is more recommended. Please do as follows.

Step 1: Select a cell to output the result. Here I select the cell C2

Step 2: Enter the below formula and press Enter

=TEXTSPLIT(A2," ")

You can see that all the text separated by spaces in A2 is split into different columns.

Step 3: Drag the formula to get all results

Select the result cells in the same row, then drag the AutoFill Handle down to get all results.

Split cells in Excel into multiple rows

As shown in the screenshot below, there is a list of order details in the range A2:A4, and the data needs to be split using a slash to extract different types of information such as Item, Quantity, Unit Price and Date. To accomplish this task, this section demonstrates 3 methods.

Split cells into multiple rows with TEXTSPLIT function

If you are using Excel for Microsoft 365, the TEXTSPLIT function method can easily help. Please do as follows.

Step 1: Select a cell to output the result. Here I select the cell B6

Step 2: Type the below formula and press Enter

=TEXTSPLIT(A2,,"/")

All text in A2 is split into separate rows based on the 'slash' separator.

To split data in cells A3 and A4 into individual rows based on slashes, simply repeat steps 1 and 2 with the appropriate formulas below.

Formula in C6:

=TEXTSPLIT(A3,,"/")

Formula in D6:

=TEXTSPLIT(A4,,"/")

Result

Conveniently split cells into multiple rows using Kutools

Although Excel's TEXTSPLIT feature is very useful, it is limited to Excel for Microsoft 365 users. Moreover, if you have multiple cells in a column to split, you will need to apply different formulas individually to each cell to get the results. In contrast, Kutools for Excel's Split Cells feature is working across all Excel versions. It provides a straightforward, efficient solution to split cells into multiple rows or columns at once with just a few clicks.

After installing Kutools for Excel, click Kutools > Merge & Split > Split Cells to open the Split Cells dialog box.

1. Select the range of cells containing the text you wish to split.
2. Select the Split to Rows option.
3. Select a delimiter you need (here I select the Other option and enter a slash), then click OK.
   
   
4. Select a destination cell and click OK to get all split data
   
   

Split cells into multiple rows with VBA code

This section provides a VBA code for you to easily split cells into multiple rows in Excel. Please do as follows.

Step 1: Open the Microsoft Visual Basic for Applications window

Press the Alt + F11 keys to open this window.

Step 2: Insert a module and enter VBA code

Click Insert > Module, and then copy and paste the following VBA code into the Module (Code) window.

VBA code: Split cells into multiple rows in Excel

Option Explicit

Sub SplitCellsToRows()
'Updated by Extendoffice 20230727
    Dim inputRng As Range
    Dim outputRng As Range
    Dim cell As Range
    Dim splitValues() As String
    Dim delimiter As String
    Dim i As Long
    Dim columnOffset As Long
    On Error Resume Next
    
    Set inputRng = Application.InputBox("Please select the input range", "Kutools for Excel", Type:=8) ' Ask user to select input range
    If inputRng Is Nothing Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    Set outputRng = Application.InputBox("Please select the output range", "Kutools for Excel", Type:=8) ' Ask user to select output range
    If outputRng Is Nothing Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    delimiter = Application.InputBox("Please enter the delimiter to split the cell contents", "Kutools for Excel", Type:=2) ' Ask user for delimiter
    If delimiter = "" Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    If delimiter = "" Or delimiter = "False" Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    
    Application.ScreenUpdating = False
    
    columnOffset = 0
    For Each cell In inputRng
        If InStr(cell.Value, delimiter) > 0 Then
            splitValues = Split(cell.Value, delimiter)
            For i = LBound(splitValues) To UBound(splitValues)
                outputRng.Offset(i, columnOffset).Value = splitValues(i)
            Next i
            columnOffset = columnOffset + 1
        Else
            outputRng.Offset(0, columnOffset).Value = cell.Value
            columnOffset = columnOffset + 1
        End If
    Next cell
    
    Application.ScreenUpdating = True
End Sub

Step 3: Run the VBA code

Press the F5 key to run the code. Then you need to do the following configurations.

1. A dialog box will appear prompting you to select the cells with the data you want to split (here I select the range A2:A4). After making your selection, click OK.
   
   
2. In the second popping up dialog box, you need to select the output range (here I select the cell B6), and then click OK.
   
   
3. In the last dialog box, enter the delimiter used to split the cell contents (here I enter a slash) and then click the OK button.
   
   

Result

Cells in the selected range are split into multiple rows at the same time.

Split cells into multiple rows with Power Query

Another method for splitting cells into multiple rows by certain delimiter is to use Power Query, which can also make the split data dynamically change with the source data. The downside to this method is that it takes multiple steps to complete. Let's dive in to see how it works.

Step 1: Select the cells you want to split into multiple rows, select Data > From Table / Range

Step 2: Convert the selected cells to table

If the selected cells are not Excel table format, a Create Table dialog box will pop up. In this dialog box, you just need to verify if Excel has picked your selected cell range correctly, mark if your table has header, and then click the OK button.
If the selected cells are Excel table, jump to Step 3.

Step 3: Choose Split Column By Delimiter

A Table – Power Query Editor window pops up, click Split Column > By Delimiter under the Home tab.

Step 4: Configure the Split Column by Delimiter dialog box

1. In the Select or enter the delimiter section, specify a delimiter for splitting the text (Here I choose Custom and enter a slash / in the textbox).
2. Expand the Advanced Options section (which is folded by default) and select the Rows option.
3. In the Quote Character section, choose None from the drop-down list;
4. Click OK.
   
   

Step 5: Save and load the split data

1. In this case, as I need to specify a custom destination for my split data, I click Close & Load > Close & Load To.
   
   
   Tip: To load the split data in a new worksheet, choose the Close & Load option.
2. In the Import Data dialog box, choose the Existing worksheet option, select a cell to locate the split data, and then click OK.
   
   

Result

Then all cells in the selected range are split into different rows within the same column by specified delimiter.

In conclusion, this article has explored different methods to split cells into multiple columns or rows in Excel. No matter which approach you choose, mastering these techniques can greatly enhance your efficiency when dealing with data in Excel. Keep exploring, and you'll find the method that works best for you.

Related Articles