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
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.
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.
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)
#6 keep the cell range to be selected, and drag the AutoFill Handle down to other cells to apply this formula.
Leave a Reply
You must be logged in to post a comment.