Split Colon-Delimited Values into Rows with Excel VBA
Question details
Split colon-separated values into separate Excel rows while duplicating the other fields from each source record.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains multiple related values in one cell, and each value needs its own row with the associated record data.
- Observed behavior
- The desired result is an expanded set of records appended below the original data, with the header and original rows preserved.
Confirm that row 1 contains headers, column B contains the colon-delimited values, and column C contains the related data. Use desktop Excel with macros enabled for trusted files, and save the workbook as an Excel Macro-Enabled Workbook (.xlsm).
Append expanded records with an Excel VBA macro
Use this macro to split column B and append one row per nonempty value while retaining the original records.
The macro processes the active worksheet. It copies values from all columns with headers, including column C, but does not copy formulas or formatting. Blank split values are skipped, and spaces around each value are removed.
In desktop Excel, select the worksheet to process, then open Developer > Visual Basic.
In the VBA editor, choose Insert > Module and paste the following code: Sub ExpandColonValues() Dim ws As Worksheet Dim lastRow As Long, lastCol As Long Dim r As Long, nextRow As Long Dim parts As Variant, part As Variant Set ws = ActiveSheet lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column If lastRow < 2 Or lastCol < 3 Then MsgBox "Check that row 1 has headers through column C and column B has data." Exit Sub End If nextRow = lastRow + 1 For r = 2 To lastRow parts = Split(CStr(ws.Cells(r, "B").Value), ":") For Each part In parts If Len(Trim(CStr(part))) > 0 Then ws.Cells(nextRow, 1).Resize(1, lastCol).Value = _ ws.Cells(r, 1).Resize(1, lastCol).Value ws.Cells(nextRow, "B").Value = Trim(CStr(part)) nextRow = nextRow + 1 End If Next part Next r End Sub
Return to the intended worksheet, choose Developer > Macros, select ExpandColonValues, and click Run.
Scroll below the original data and verify that each nonempty value from column B has its own row and that the corresponding fields, including column C, match the source record.

Split colon-delimited records in WPS Spreadsheets
For a smaller dataset, use Text to Columns and Paste Special in WPS Spreadsheets to turn each colon-delimited cell into rows, then copy the related fields beside them.
- 1. Open the workbook: Open the Excel workbook in WPS Spreadsheets and identify the source cells in column B and their related fields.
- 2. Split one source cell: Copy a column B cell to an empty scratch worksheet. Select the pasted cell, choose Data > Text to Columns, select a delimited option, and enter a colon as the delimiter.
- 3. Transpose the values: Copy the split cells across the scratch row. On the original worksheet, select the first empty cell in column B below the data, then use Paste Special > Transpose to paste the values vertically.
- 4. Copy the related fields: Copy the original record's other fields, including column C, into the corresponding columns beside each newly pasted value. Repeat the split, transpose, and copy steps for each source record.
- 5. Review and save: Check the expanded rows against the original records, remove the scratch worksheet if it is no longer needed, and use File > Save As to save the result.

Frequently Asked Questions
Can I split values from column A instead of column B?
Yes. In the VBA editor, change the references to column B in the macro to column A. Also verify that the output assignment writes each split value to the intended column.
Does the macro remove the original rows?
No. It leaves the header and source records in place and appends the expanded rows below the last populated cell in column B.
What happens if a cell contains consecutive colons?
Consecutive colons create an empty split value. The macro skips that value and does not create an empty output row.
Why should I save the workbook as an XLSM file?
An XLSM file can retain the VBA macro. In Excel, choose File > Save As and select Excel Macro-Enabled Workbook if you want to keep the code in the workbook.




