How to Split Comma separated Values into Columns or Rows in Excel

,

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.

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.

split comma separated values1

#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.

split comma separated values2

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

split comma separated values3

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

split comma separated values4

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

split comma separated values5

split comma separated values6

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

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.

Get the position of the nth using excel vba1

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

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

convert column number to letter3

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

split comma separated values7

Function SplitValues(a As String, b As String)
    Dim Text() As String
    Text = Split(b, a)
    SplitValues = Text
End Function

#5 back to the current worksheet,  and select the cell range C1:F1,  then type the following formula in a blank cell, and then press Ctrl  + Shift + Enter keys on your keyboard to change it as array formula.

=SplitValues(“,”,B1)

split comma separated values8

#6 keep the cell range to be selected, and drag the AutoFill Handle down to other cells to apply this formula.

split comma separated values9

 

 

Leave a Reply