Use VBA Split to Extract Comma-Separated Values in Excel
Question details
The user wants to split comma-separated values with VBA and use individual values in cells or copied forms.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A cell contains comma-separated values, or a multi-select list box supplies items for repeated form copies.
- Observed behavior
- Each value should be written to a separate cell or assigned to its corresponding copied form.
Use a desktop version of Excel that supports VBA, and identify the input cell and empty destination cells. For the form workflow, identify the multi-select list box, the form-copying code, and the field that should receive each selection.
Split a cell value and write the results across a row
Use this VBA macro when a cell contains comma-separated text and each item should appear in a separate cell.
In Excel, open the workbook and press Alt+F11 to open the Visual Basic Editor.
Choose Insert > Module and paste this code: Sub SplitCellValues() Dim data As Variant Dim i As Long data = Split(CStr(Range("A1").Value), ",") For i = LBound(data) To UBound(data) Range("B1").Offset(0, i).Value = Trim(data(i)) Next i End Sub
Enter comma-separated text in A1, make sure B1 and the cells to its right are available, then run SplitCellValues from the VBA editor. The macro writes the items across the row, starting in B1.

Use selected list-box items in copied forms
Use the list box's selected items directly when creating one form copy per selection.
Work with comma-separated spreadsheet data in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility for an easier move between applications. It does not fix Excel VBA or guarantee that an existing macro or form will run unchanged.

Frequently Asked Questions
How do I write the split values down a column instead?
In the VBA loop, change Range("B1").Offset(0, i) to Range("B1").Offset(i, 0). The results will start in B1 and continue downward.
How can I remove spaces around each split value?
Use Trim(data(i)) when assigning an array item to a cell, as in the macro above. This removes leading and trailing spaces from each item.
Do I need Split for a multi-select list box?
No. Loop from 0 to ListCount - 1, check Selected(i), and read each selected item with List(i). Add your form-copy and field-assignment code inside the selection check.
How do I keep the VBA macro in my workbook?
In Excel, choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). Follow your organization's security policy when enabling macros.




