VBA & Macro Problems

Use VBA Split to Extract Comma-Separated Values in Excel

Steve KSteve K Nov 14, 2026 868 views

Question details

The user wants to split comma-separated values with VBA and use individual values in cells or copied forms.

Use VBA Split to Extract Comma-Separated Values in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In Excel, open the workbook and press Alt+F11 to open the Visual Basic Editor.

2
Insert the macro

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

3
Run and check the results

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.

Split a cell value and write the results across a row
Choose the destination carefully: The macro writes to the active worksheet and overwrites destination cells. Split treats every comma as a separator; it does not parse quoted CSV fields containing commas.
Free Microsoft Office alternative

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.

Organize and split comma-separated spreadsheet data.Open and edit Microsoft Excel XLSX files.Use a familiar spreadsheet interface to ease migration.Work with a free, lightweight office suite.
microsoft office alternative - wps office

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.