How to split data by delimiter and rearrange it into a column in Excel?

When working with Excel, it's common to encounter cells that contain multiple values separated by commas, semicolons, or other delimiters. If you need to split a range of such cells by a specific delimiter and consolidate all the resulting values into a single column as below screenshot shown, there are efficient methods to achieve this for better data organization and analysis.

Split data by delimiter and rearrange it into a column

• By using a formula (Excel 365)
• By using Kutools for Excel
• By using VBA code

Split data by delimiter and rearrange it into a column with a formula (Excel 365)

In Excel 365, we can achieve this transformation dynamically using modern functions like TEXTSPLIT and TEXTJOIN functions.

Click on the cell where you want to put the result, enter the below formula, and press Enter key. The cells containing the comma separated text strings are converted to a list at once. See screenshot:

=TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ", TRUE, A2:A5), ", "))

Split data by delimiter and rearrange it into a column with Kutools for Excel

For users working with legacy Excel versions (2016/2019 etc. lacking TEXTSPLIT function), Kutools for Excel's Split Data to Rows feature offers an ideal solution. This feature allows you to quickly split cell contents into a single column based on a specific delimiter, saving you time and effort.

After installing Kutools for Excel, please do with the following steps:

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, choose Other, then type your delimiter (e.g., a comma and a space) to define how the cell content should be split;
   • Finally, click OK button.
     

Result: The selected cells are split into a column by the specified delimiters.

Split data by delimiter and rearrange it into a column with VBA code

Here, we’ll walk you through how to use VBA to split cell contents by a delimiter and rearrange the results into one column.

1. Press Alt + F11 keys to display Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste below code to the new Module window.

VBA: Split and Transpose

Sub Vertical()
'Updateby Extendoffice
    Dim i As Long, strTxt As String
    Dim startP As Range
    Dim xRg As Range, yRg As Range
    On Error Resume Next
    Set xRg = Application.InputBox _
    (Prompt:="Range Selection...", _
    Title:="Kutools For Excel", Type:=8)
    i = 1
    Application.ScreenUpdating = False
    For Each yRg In xRg
        If i = 1 Then
            strTxt = yRg.Text
            i = 2
        Else
            strTxt = strTxt & "," & yRg.Text
        End If
    Next
    Application.ScreenUpdating = True
    Set startP = Application.InputBox _
    (Prompt:="paste range...", _
    Title:="Kutools For Excel", Type:=8)
    ary = Split(strTxt, ",")
    i = 1
    Application.ScreenUpdating = False
    For Each a In ary
        startP(i, 1).Value = a
        i = i + 1
    Next a
    Application.ScreenUpdating = True
End Sub

3. Press F5 key to run the code, a dialog pops out for you to select a range to split and transpose.

4. Click OK. A dialog pops out to select a location to place the data.

5. Click OK, and the selection has been split and transposed by comma.

🔚 Conclusion

All methods effectively solve the core problem of splitting delimited data, with the optimal choice depending on your technical comfort level and specific requirements.

• For most Excel 365 users, the formula approach offers the best balance of power and maintainability.
• For frequent, complex transformations by non-programmers, Kutools is ideal.
• For customized, automated solutions in enterprise environments, VBA provides the most control.

Choose the method based on your specific needs. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials to help you master Excel.