How to split multiline cell contents into separated rows/columns in Excel?
In many practical scenarios, you may find that cell content in Excel is displayed on multiple lines within a single cell. This typically happens when users press Alt + Enter while entering data, creating line breaks (carriage returns) within the cell. For data analysis, sorting, or further processing, it is often necessary to split these multiline contents so that each item occupies an individual row or column. However, Excel does not provide a straightforward built-in setting for this, which can make the task challenging for many users. This article demonstrates several effective ways to quickly split multiline cell contents—separated by Alt + Enter—into distinct rows or columns.
Split multiline cell contents into separate columns with Text to Columns function
Easily split multiline cell contents into separate rows or columns with Kutools for Excel
Split multiline cell contents into rows or columns using VBA macro
Split multiline cell contents into separate columns with Text to Columns function
When dealing with data containing line breaks (inserted using Alt + Enter), the Text to Columns function can be used to split the content into separate columns efficiently. This method is especially useful if you need to expand each line break-delimited entry horizontally across multiple columns on the same row. However, note that this approach only separates data into columns, not rows.
1. Select the cells with multiline contents you want to split to separate columns, then click Data > Text to Columns. See screenshot:
2. In the first Convert Text to Columns Wizard dialog box, select Delimited option, and then click the Next button.
3. In the second Convert Text to Columns Wizard dialog box, only check the Other box, hold the Alt key and enter number 0010 into the following blank box. Then click the Next button. See screenshot:
This step is crucial: entering Alt+0010 corresponds to the line break character that Excel uses to separate lines in a cell. Make sure to hold down the Alt key while typing 0010 on the numeric keypad, so the delimiter is recognized correctly. If you use a laptop without a numeric keypad, you may need to activate the NumLock or use the onscreen keyboard for accurate input.
4. In the third Convert Text to Columns Wizard dialog box, select a cell for placing the split contents in the Destination box, and finally click the Finish button.
By default, Excel will overwrite the existing data to the right of your selected cells when splitting. To prevent data loss, you may want to specify a destination cell that is not in line with other important data, or copy your original data to a new area before splitting.
Then the selected multiline cells’ contents are split into separated columns as below screenshot shown.
Note: If you notice that empty columns are produced, this might be due to trailing line breaks or extra spaces at the end of your cell content. You can manually remove those or use Excel’s TRIM or CLEAN functions for preprocessing.
Split multiline cell contents into separate rows or columns with Kutools for Excel
If you need to split multiline cell contents into multiple rows (not just columns), the basic Text to Columns function in Excel is not sufficient. In such cases, the Split Cells utility provided by Kutools for Excel can be a more versatile and convenient choice. This tool allows you to choose whether to split your data across rows or columns based on line breaks, offering greater flexibility and efficiency—especially useful for large data sets or repeated tasks.
1. Select the cells containing multiline entries that you want to separate, then click Kutools > Merge & Split > Split Cells. See screenshot:
2. In the Split Cells dialog box, complete the following steps:
- (1) Select Split to Rows if you wish to lay out each line on a new row; otherwise, choose Split to Columns for a column-wise split.
- (2) Under Split by, select the New line option to use line breaks as the delimiter.
- (3) Click the OK button to confirm your settings.
This step is flexible—ensure you select the separator that matches your data structure, and review the preview for accuracy before continuing.
3. When a dialog box appears, select a blank cell to specify where the split text will be placed, then click OK.
The multiline cells are now split into separate columns or rows as shown below.
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
Split multiline cell contents into rows or columns using VBA macro
For users who are comfortable with automating tasks, a VBA macro offers an efficient and customizable way to split multiline cells by line breaks and output each item into separate rows or columns. This approach is ideal for bulk processing, for creating reusable solutions, or for splitting data in-place across large tables.
With VBA, you can control the target output (whether rows or columns), fine-tune delimiters, and automate repetitive workflows—significantly saving time for power users or when handling large datasets.
1. Access the VBA Editor by clicking Developer Tools > Visual Basic. In the Microsoft Visual Basic for Applications window, click Insert > Module, and copy the following code into the blank module window:
Sub SplitMultilineCellsToRows()
' Split selected multiline cell contents to separate rows below each cell
On Error Resume Next
Dim Cell As Range
Dim SplitArr() As String
Dim i As Integer
xTitleId = "KutoolsforExcel"
For Each Cell In Selection
If InStr(Cell.Value, Chr(10)) Then
SplitArr = Split(Cell.Value, Chr(10))
For i = UBound(SplitArr) To 1 Step -1
Cell.Offset(i, 0).EntireRow.Insert
Cell.Offset(i, 0).Value = SplitArr(i)
Next i
Cell.Value = SplitArr(0)
End If
Next Cell
End Sub2. To run the code, select the target cells in your worksheet, click the 
button or press F5 in the VBA window. Your multiline cell content should be instantly split into rows.
To adapt this code for splitting into columns, use the following macro:
Sub SplitMultilineCellsToColumns()
' Split selected multiline cell contents to separate columns to the right
On Error Resume Next
Dim Cell As Range
Dim SplitArr() As String
Dim i As Integer
xTitleId = "KutoolsforExcel"
For Each Cell In Selection
If InStr(Cell.Value, Chr(10)) Then
SplitArr = Split(Cell.Value, Chr(10))
For i = 0 To UBound(SplitArr)
Cell.Offset(0, i).Value = SplitArr(i)
Next i
End If
Next Cell
End SubAgain, select the target cells, return to the VBA editor, and run this macro. The values from each multiline cell will be split into columns to the right.
Tips, precautions, and troubleshooting: When using VBA solutions, always save your Excel file beforehand. Macros cannot be undone with the regular Undo shortcut, and newly inserted rows/columns may overwrite existing data if you do not allow enough empty space in your worksheet. Run tests on sample data when using VBA for the first time.
Easily split multiline cell contents into separate rows or columns with Kutools for Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in