How to Merge Excel Cells with a Line Break in Bulk
Question details
Users need an efficient way to merge data from multiple Excel columns into a single cell, separated by line breaks, specifically when handling large numbers of records.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing and formatting large datasets, such as CSV files, where data across multiple columns needs to be consolidated into one column with each item on a new line.
- Observed behavior
- Instead of manual entry, users require an automated formula that combines values and forces a new line for each merged item within the cell.
Insert a new blank column next to your data where the merged results will be displayed. Please note that Windows Excel uses CHAR(10) for line breaks, while Mac Excel historically uses CHAR(13).
Use the TEXTJOIN Function
The TEXTJOIN function is the most efficient method to combine multiple cells with a line break because it automatically ignores blank cells.
Available in newer versions of Excel, TEXTJOIN lets you specify a delimiter (like a line break) and apply it across an entire range of cells simultaneously.
Click on your destination cell and type =TEXTJOIN(CHAR(10), TRUE, D5:E5). Make sure to replace D5:E5 with the actual range of cells you want to merge.
Select the cell containing your formula, navigate to the Home tab on the top ribbon, and click on the Wrap Text button to make the line breaks visible.
Hover over the bottom-right corner of the cell until the fill handle (a small plus sign) appears, then double-click or drag it down to apply the formula to all records in bulk.
Use the CONCATENATE Function
If you are using an older version of Excel that does not support TEXTJOIN, you can use the CONCATENATE function to achieve the same result manually.
Combine with an IF Statement for Conditional Merging
For complex databases where you only want to insert a line break if the adjacent cell actually contains data, combining your formula with an IF statement works best.
Merge Cells Effortlessly in WPS Spreadsheet
WPS Spreadsheet fully supports advanced text functions like TEXTJOIN and CONCATENATE. You can easily merge large datasets and apply formatting like Wrap Text in a familiar, intuitive interface.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook or CSV file containing the data you want to merge.
- 2. Apply the text formula: Select a blank cell and input =TEXTJOIN(CHAR(10), TRUE, A1:B1) to combine the adjacent fields.
- 3. Wrap text and copy down: Click the Wrap Text button found in the Home tab, then drag the cell's fill handle downward to process your records in bulk.
Frequently Asked Questions
Why is my merged text showing on a single line instead of breaking?
If your combined text appears on one line despite using the CHAR(10) formula, you need to enable the Wrap Text feature. Select the cells, navigate to the Home tab, and click Wrap Text to reveal the hidden line breaks.
What does CHAR(10) do in an Excel formula?
CHAR(10) returns the invisible line break character in Windows environments. By inserting it between your cell references in a merging formula, it pushes the subsequent text to a new line within the same cell.
How do I remove the formulas but keep the merged text with line breaks?
To convert your formula results into static text, highlight the column with your merged data, copy it (Ctrl+C), right-click the same area, and choose Paste as Values. This removes the underlying formulas while preserving your text and line breaks.
