Split Line-Break Data into Rows Across Excel Columns with VBA
Question details
Split line-break-separated values in multiple Excel columns into separate rows and repeat the related first-column value.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet has a related value in the first column and multiline data in other columns. Each line should occupy a separate row.
- Observed behavior
- The desired result is one row per line position, with corresponding values aligned across columns and the first-column value repeated.
Use desktop Excel with a header row, a value in column A for every data row, and headers extending through the last column to process. Ensure the data area has no merged cells or error values; the macro inserts entire worksheet rows and replaces processed cells with text values.
Use a VBA macro to split and align multiline cells
Process rows from bottom to top so inserted rows do not disrupt data that has not yet been processed.
The macro aligns lines by position across columns B through the last header column. If a cell has fewer lines than another cell in the same source row, its remaining output cells are left blank.
In desktop Excel, open the worksheet you want to process, then select Developer > Visual Basic. In the VBA editor, select Insert > Module.
Paste this code into the module: Sub SplitMultilineRows() Dim ws As Worksheet Dim lastRow As Long, lastCol As Long Dim r As Long, c As Long, i As Long, maxParts As Long Dim textValue As String, parts As Variant, firstValue As Variant Set ws = ActiveSheet lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column If lastRow < 2 Or lastCol < 2 Then Exit Sub For r = lastRow To 2 Step -1 maxParts = 1 firstValue = ws.Cells(r, 1).Value2 For c = 2 To lastCol textValue = CStr(ws.Cells(r, c).Value2) textValue = Replace(Replace(textValue, vbCrLf, vbLf), vbCr, vbLf) parts = Split(textValue, vbLf) If UBound(parts) + 1 > maxParts Then maxParts = UBound(parts) + 1 Next c If maxParts > 1 Then ws.Rows(r + 1).Resize(maxParts - 1).Insert End If For i = 1 To maxParts - 1 ws.Cells(r + i, 1).Value2 = firstValue Next i For c = 2 To lastCol textValue = CStr(ws.Cells(r, c).Value2) textValue = Replace(Replace(textValue, vbCrLf, vbLf), vbCr, vbLf) parts = Split(textValue, vbLf) For i = 0 To maxParts - 1 If i <= UBound(parts) Then ws.Cells(r + i, c).NumberFormat = "@" ws.Cells(r + i, c).Value2 = parts(i) Else ws.Cells(r + i, c).ClearContents End If Next i Next c Next r End Sub
Return to Excel and activate the intended worksheet. Select Developer > Macros, choose SplitMultilineRows, and select Run.
Review the inserted rows and aligned values. To keep the macro, select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm).

Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move everyday spreadsheet work between office suites. It does not fix Excel VBA issues, and macro support can vary by WPS version and platform.

Frequently Asked Questions
What happens when columns have different numbers of lines?
The macro creates enough rows for the cell with the most lines. Columns with fewer lines have blank cells in the remaining output rows.
Why does the macro process rows from the bottom up?
Inserting rows below a source row can shift other rows. Starting with the last data row prevents those insertions from changing the positions of rows still to be processed.
Does the macro repeat values from columns other than column A?
No. It repeats only column A. Columns B through the last header column are split by line position, and missing positions are left blank.
Will the macro preserve formulas in the processed columns?
No. It reads cell values and writes the split results as text, so formulas in columns B onward are replaced. Review the worksheet before selecting Developer > Macros > Run.
How do I keep the macro in my workbook?
In Excel, select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). An XLSX file cannot retain the VBA code.




