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
- Split Comma Separated Values into Rows or Columns with VBA Macro
Table of Contents
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.
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.
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.
#6 keep the cell range to be selected, and drag the AutoFill Handle down to other cells to apply this formula.