VBA & Macro Problems

Split Colon-Delimited Values into Rows with Excel VBA

Emma BrownEmma Brown Nov 20, 2026 868 views

Question details

Split colon-separated values into separate Excel rows while duplicating the other fields from each source record.

Split Colon-Delimited Values into Rows with Excel VBA
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.
Before you start

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).

Solution 1Recommended

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.

1
Open the VBA editor

In desktop Excel, select the worksheet to process, then open Developer > Visual Basic.

2
Insert the macro

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

3
Run the macro

Return to the intended worksheet, choose Developer > Macros, select ExpandColonValues, and click Run.

4
Check the appended records

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.

Append expanded records with an Excel VBA macro
Check the source range before running: The macro finds the last source row using column B and appends output immediately below it. Make sure column B extends to the last source record and that the rows below the data are empty. Running the macro again will append additional records.
Split values without VBA

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. 1. Open the workbook: Open the Excel workbook in WPS Spreadsheets and identify the source cells in column B and their related fields.
  2. 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. 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. 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. 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.
Split colon-separated values without writing a macroOpen and edit Microsoft Excel XLSX workbooksKeep original records while adding expanded rowsUse familiar spreadsheet editing tools
microsoft office alternative - wps office

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.