How to split cell values vertically in Excel?

When working in Excel, you may encounter situations where multiple values in a single row or multiple rows need to be converted into a vertical list. This is especially useful when data is stored in a horizontal format but needs to be processed or analyzed in a column format.

This guide will cover three efficient methods to convert cell values vertically:

Split cell values into rows vertically in Excel

• Using a Formula (Excel 365) – Best for dynamic and automatic transformations
• Using Kutools for Excel – A quick and easy solution with a specialized tool for All Excel versions
• Using VBA Code – Ideal for automating the process for All Excel versions

Split cell values into rows vertically by a Formula (Excel 365)

If you have Excel 365, you can use the TEXTSPLIT, TEXTJOIN, and TRANSPOSE functions to convert cell values vertically without manual effort.

Please copy and paste the following formula into a blank cell, and press "Enter" key, Excel will automatically spill the data into multiple rows, see screenshot:

=TRANSPOSE(TEXTSPLIT(TEXTJOIN(";", TRUE, A1:A4), ";"))

Split cell values into rows vertically by Kutools for Excel

If you use Excel 2019 or earlier, you can use "Kutools for Excel" – a powerful add-in with specialized tools to transform and manage data easily. With its "Split Data to Rows" feature, you can seamlessly convert cell values vertically based on any delimiter you need.

After installing "Kutools for Excel", please do as this:

1. Click "Kutools" > "Merge & Split" > "Split Data to Rows", see screenshot:
   
2. In the "Split Data to Rows" dialog box, specify the following operations:
   • Select the cells that you want to split;
   • In the "Delimiter" section, select the delimiter to define how the cell content should be split. Here, I will select "semicolon".
   • Finally, click "OK" button.
     

Result: The data will be converted into a vertical column.

Split cell values into rows vertically by VBA code

For advanced users, VBA provides a robust solution to automate the vertical splitting of cell values. The VBA code below enables you to effortlessly split cell contents into rows using any custom separator.

1. Hold down the "ALT" + "F11" keys to open the "Microsoft Visual Basic for Applications" window.

2. Click "Insert" > "Module", and paste the following code in the "Module" Window.

VBA code: Split cell values into rows vertically by specific separator

Sub splitvertically()
'updatebyExtendoffice
    Dim xRg As Range
    Dim xOutRg As Range
    Dim xCell As Range
    Dim xTxt As String
    Dim xStr As String
    Dim xOutArr As Variant
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("please select the data range:", "Kutools for Excel", xTxt, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    Set xOutRg = Application.InputBox("please select output cell:", "Kutools for Excel", , , , , , 8)
    If xOutRg Is Nothing Then Exit Sub
    For Each xCell In xRg
        If xStr = "" Then
            xStr = xCell.Value
        Else
            xStr = xStr & ";" & xCell.Value
        End If
    Next
    xOutArr = VBA.Split(xStr, ";")
    xOutRg.Range("A1").Resize(UBound(xOutArr) + 1, 1) = Application.WorksheetFunction.Transpose(xOutArr)
End Sub

3. Then press "F5" key to run this code, and a prompt box will pop out to remind you select the cell values that you want to split vertically, see screenshot:

4. And then click "OK" button, another box will pop out to remind you select a cell to put the result, see screenshot:

5. Click "OK" button, and the cell values have been split into a list by a separator, see screenshot:

Converting cell values vertically in Excel can be achieved through multiple methods, each with its own strengths. By understanding these methods, you can choose the best approach for your specific needs and optimize your workflow in Excel. Whether you’re a beginner or an advanced user, these tools and techniques will help you handle data more effectively and efficiently. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials to help you master Excel.