VBA & Macro Problems

Split Newline-Separated Excel Cells into Rows with VBA

Olivia MillerOlivia Miller Nov 18, 2026 870 views

Question details

Split line-separated descriptions and amounts in an Excel cell into separate rows while repeating identifying values.

Split Newline-Separated Excel Cells into Rows with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
Each source row has an ID, a name, and a cell containing one or more descriptions and amounts separated by line breaks.
Observed behavior
The desired result is one row per line, with the ID and name repeated and the description and amount placed in separate columns.
Before you start

Arrange the worksheet with headers in row 1, IDs in column A, names in column B, and line-separated items in column C. The macro writes amounts to column D, inserts entire rows, and assumes each item uses the separator ': $'; check for existing data in column D and other columns before running it.

Solution 1Recommended

Use a VBA macro to split each line into a separate row

Use this method in desktop Excel when each item follows a format such as 'Service: $25' and you want to repeat the ID and name.

The macro processes source rows from bottom to top so inserted rows do not disrupt rows still waiting to be processed. It also processes single-line cells.

For inserted rows, only columns A and B are repeated. Items without ': $' are kept as descriptions with a blank amount, and blank lines produce blank items.

1
Open the VBA editor

In desktop Excel, select the worksheet to process, then open Developer > Visual Basic. If the Developer tab is hidden, enable it in Excel's ribbon settings.

2
Insert the macro

In the VBA editor, choose Insert > Module and paste this code: Sub SplitCellsIntoRows() Dim ws As Worksheet Dim lastRow As Long, r As Long, i As Long Dim lines As Variant, text As String Dim previousScreenUpdating As Boolean Set ws = ActiveSheet previousScreenUpdating = Application.ScreenUpdating On Error GoTo CleanUp Application.ScreenUpdating = False lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row For r = lastRow To 2 Step -1 text = CStr(ws.Cells(r, "C").Value) text = Replace(text, vbCrLf, vbLf) text = Replace(text, vbCr, vbLf) lines = Split(text, vbLf) For i = UBound(lines) To 1 Step -1 ws.Rows(r + 1).Insert ws.Cells(r + 1, "A").Value = ws.Cells(r, "A").Value ws.Cells(r + 1, "B").Value = ws.Cells(r, "B").Value WriteSplitItem ws, r + 1, CStr(lines(i)) Next i WriteSplitItem ws, r, CStr(lines(0)) Next r CleanUp: Application.ScreenUpdating = previousScreenUpdating If Err.Number <> 0 Then MsgBox "Macro stopped: " & Err.Description, vbExclamation End Sub Private Sub WriteSplitItem(ByVal ws As Worksheet, ByVal rowNum As Long, ByVal item As String) Dim separatorPos As Long separatorPos = InStr(1, item, ": $", vbBinaryCompare) If separatorPos > 0 Then ws.Cells(rowNum, "C").Value = Left(item, separatorPos - 1) ws.Cells(rowNum, "D").Value = Mid(item, separatorPos + 3) Else ws.Cells(rowNum, "C").Value = item ws.Cells(rowNum, "D").ClearContents End If End Sub

3
Run the macro

Return to the worksheet, select Developer > Macros, choose SplitCellsIntoRows, and select Run. Review columns C and D and format column D as Currency if needed.

Use a VBA macro to split each line into a separate row
Check the source layout: The last source row is determined from column A, so its final data row must have an ID. The macro replaces column C and clears or overwrites column D on processed rows; it does not repeat values from other columns.
Free Microsoft Office alternative

Work with spreadsheets in WPS Office

WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface. Open compatible Excel workbooks and continue editing with a straightforward transition from Microsoft Office. The Excel VBA macro above is intended for desktop Excel; macro support in WPS may vary.

Edit spreadsheet data in a familiar interfaceOpen and save common Excel formats such as .xlsx and .xlsReview and organize descriptions and amounts in spreadsheet columnsUse a lightweight office suite for everyday documents
microsoft office alternative - wps office

Frequently Asked Questions

Can I run this macro in Excel for the web?

No. Open the workbook in desktop Excel and use Developer > Macros to run the VBA procedure.

What happens if an item does not contain ': $'?

The macro leaves the entire item in column C and clears column D for that row. Update the separator in the code or standardize the source text if your data uses a different pattern.

Will the macro repeat values from other columns?

No. It repeats only the ID in column A and the name in column B on inserted rows. Other columns are not copied into those rows.

How do I keep the macro in the workbook?

In Excel, choose File > Save As and select Excel Macro-Enabled Workbook (.xlsm). You can also save the completed data separately as an .xlsx file, which does not retain VBA macros.