This post will guide you how to separate comma delimited cells to new rows or columns in Excel. How do I split comma separated values into rows with Text to Column Feature in Excel. How to convert comma separated text into rows or columns with VBA Macro in Excel.

1. Split Comma Separated Values into Rows or Columns with Text To Columns

Assuming that you have a list of data in range B1:B5, in which contain text string separated by comma characters. And you need to split those comma-separated text string into different columns in Excel. How to do it. You can use the Text To Columns Feature to achieve the result in Excel. Here are the steps:

#1 select the range of cells B1:B5 that you want to split text values into different columns.

#2 go to DATA tab, click Text to Columns command under Data Tools group. And the Convert Text to Columns Wizard dialog box will open.

#3 select the Delimited radio option in the first Convert Text to Columns Wizard dialog box, and click Next button.

#4 only check the Comma Check box under Delimiters section, and click Next button.

#5 select one cell as the destination to place the last values. And click Finish button.

You would notice that all comma-separated text values in the selected range of cells have been split into the different columns.

2. Split Comma Separated Values into Rows or Columns with VBA Macro

You can also write a simple User Defined Function with VBA code to achieve the same result of splitting comma separated values into different columns. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

#4 paste the below VBA code into the code window. Then clicking “Save” button.

Sub SplitCommaSeparatedValues()
    Dim sourceRange As Range
    Dim destinationRange As Range
    
    On Error Resume Next
    ' Prompt user to select the source cell
    Set sourceRange = Application.InputBox("Select the cell with comma-separated values", Type:=8)
    On Error GoTo 0
    
    ' Exit if the user cancels the selection
    If sourceRange Is Nothing Then
        Exit Sub
    End If
    
    On Error Resume Next
    ' Prompt user to select the destination cell
    Set destinationRange = Application.InputBox("Select the destination cell", Type:=8)
    On Error GoTo 0
    
    ' Exit if the user cancels the selection
    If destinationRange Is Nothing Then
        Exit Sub
    End If
    
    ' Split comma-separated values
    sourceRange.TextToColumns Destination:=destinationRange, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False
End Sub

#5 Close the VBA editor by clicking the “X” button or pressing ‘Alt + Q.’ Return to your Excel workbook and press ‘Alt + F8‘ to open the “Macro” dialog box.

#6 In the “Macro” dialog box, choose the macro named “SplitCommaSeparatedValues” and click ‘Run‘ button.

#7 The macro will prompt you to select the cell with comma-separated values. Click on the desired cell. click ok button. The macro will then prompt you to select the destination cell. Click on the desired destination cell. click ok button.

The comma-separated values from the source cell will be dynamically split into separate columns or rows, and the results will be placed in the selected destination cell.

3. Video: Split Comma Separated Values into Rows or Columns

This Excel video tutorial where we’ll explore two efficient methods for splitting comma-separated values into columns or rows – Using ‘Text To Columns’ feature and VBA code.

https://youtu.be/bufDpr9LO2I