VBA & Macro Problems

Split Comma-Separated Input into Excel Cells with VBA

John WilsonJohn Wilson Nov 14, 2026 868 views

Question details

Use an Excel VBA macro to remove spaces from comma-separated input and place each value in a separate cell from M2 through AH2.

Split Comma-Separated Input into Excel Cells with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user wants to enter a comma-separated list in an InputBox and populate a row of cells.
Observed behavior
Each input value should occupy one cell in M2:AH2, with spaces removed.
Before you start

Make sure Excel allows you to run macros and that M2:AH2 on the target worksheet can be cleared. The range holds up to 22 values, and the macro removes all ordinary spaces, including spaces within values.

Solution 1Recommended

Use an InputBox and Split to Fill M2:AH2

Run a VBA macro to collect the list, check its length, and write each value across the active worksheet.

1
Open the VBA editor

In Excel, open the workbook, select the worksheet you want to update, and press Alt+F11 to open the Visual Basic Editor.

2
Insert the macro

Choose Insert > Module and paste this code: Sub SplitInputValues() Dim s As String Dim values() As String Dim i As Long Dim ws As Worksheet Set ws = ActiveSheet s = Replace(InputBox("Enter values separated by commas"), " ", "") If Len(s) = 0 Then Exit Sub values = Split(s, ",") If UBound(values) + 1 > 22 Then MsgBox "Enter no more than 22 values." Exit Sub End If ws.Range("M2:AH2").ClearContents For i = 0 To UBound(values) ws.Range("M2").Offset(0, i).Value = values(i) Next i End Sub

3
Save and run

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Return to the target worksheet, press Alt+F8, select SplitInputValues, and choose Run.

4
Enter the values

In the InputBox, enter a list such as apple, pear, plum and select OK. The values appear in M2, N2, and O2.

Use an InputBox and Split to Fill M2:AH2
Check the input and destination: The macro clears all contents in M2:AH2 before writing, but leaves them unchanged if the input is empty or contains more than 22 comma-separated entries. Consecutive commas count as empty entries.
Work with spreadsheets in WPS Office

Run the InputBox Macro in WPS Spreadsheet

In a WPS Spreadsheet installation with VBA macro support enabled, use the same macro to split comma-separated input across M2:AH2. VBA availability can vary by edition and installation.

  1. 1. Open the workbook: Open WPS Spreadsheet and use File > Open to select your workbook.
  2. 2. Check VBA access: Look for Developer > Visual Basic. If it is unavailable, check whether your WPS edition and installation support VBA macros.
  3. 3. Add the macro: Open Visual Basic, choose Insert > Module, and paste the SplitInputValues code from the solution above.
  4. 4. Run and save: Select the target worksheet, run SplitInputValues from the Developer macro controls, and enter your comma-separated list. Save the workbook as an .xlsm file to retain the macro.
Split comma-separated input into separate spreadsheet cellsOpen and save Microsoft Excel macro-enabled .xlsm filesWork in a familiar spreadsheet interfaceUse WPS Office for free spreadsheet editing
microsoft office alternative - wps office

Frequently Asked Questions

How many values can I enter?

M2:AH2 contains 22 cells, so the macro accepts at most 22 comma-separated entries. If you enter more, it displays a message and does not clear the range.

Does the macro remove spaces inside a value?

Yes. Replace(s, " ", "") removes every ordinary space, so an entry such as New York becomes NewYork.

What happens if I cancel the InputBox?

Canceling or submitting an empty input causes the macro to exit without changing M2:AH2.

How do I keep the macro in my workbook?

In Excel, choose File > Save As and select Excel Macro-Enabled Workbook (.xlsm). Enable macros when prompted if you trust the workbook.

What happens if I enter two commas together?

Split treats the space between consecutive commas as an empty entry, which counts toward the 22-entry limit and leaves the corresponding destination cell empty.