How to Wrap Formula Text onto Multiple Lines in an Excel Cell
Question details
The user wants to combine data from multiple fields (such as company, contact, address, city, and country) into a single spreadsheet cell, ensuring each piece of data is displayed on a separate line.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating separated data columns into a single, multi-line address block for easier viewing or label printing.
- Observed behavior
- Standard concatenation merges the text onto a single continuous line, and some label printing plugins misinterpret the line breaks within cells, printing each line as a separate label.
Ensure you have your separate data columns (such as company, contact, and address) organized, and note the specific destination cell where you want the consolidated multi-line text to appear.
Use the CHAR(10) Formula and Enable Wrap Text
Combine your cell references using the ampersand (&) operator alongside the CHAR(10) function to insert line breaks between the combined text items.
In Excel formulas, you cannot simply press 'Alt + Enter' to create a line break within the formula bar. Instead, you must use the CHAR(10) function, which represents the ASCII line-break character. After applying the formula, the cell must be formatted to wrap text; otherwise, the line breaks will remain invisible.
Select the cell where you want the combined text to appear. Type your formula using the ampersand (&) and CHAR(10). For example: =A2&CHAR(10)&B2&CHAR(10)&C2&CHAR(10)&D2&CHAR(10)&E2.
Press Enter. Initially, the text may appear as a single, continuous line without visible breaks.
Select the cell containing your new formula, navigate to the 'Home' tab on the ribbon, and click 'Wrap Text'. The text will instantly adjust to display each data point on a new line.
Convert Multi-Line Cells to Plain Text for Label Printing
Use this workaround if your third-party label printing software (like the DYMO LabelWriter plug-in) incorrectly prints each line of the combined cell onto a separate label.
Combine Multi-Line Cell Data Easily with WPS Spreadsheet
WPS Spreadsheet makes it simple to manage, combine, and format large datasets. It natively supports standard line break formulas like CHAR(10) and features an intuitive interface to wrap text instantly.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your spreadsheet document containing the separate data columns.
- 2. Input the concatenation formula: In an empty cell, use the formula combining cell references and line breaks: =A2&CHAR(10)&B2.
- 3. Enable Text Wrapping: Select your newly populated cell, go to the 'Home' tab, and click the 'Wrap Text' icon to make the line breaks visible.
- 4. Adjust column and row sizing: Click and drag the row or column borders to ensure all wrapped lines of text are comfortably visible within the cell.
Frequently Asked Questions
Why is the CHAR(10) formula not creating a visible line break in my Excel cell?
By default, spreadsheet software does not automatically display manual line breaks generated by a formula. You must manually select the cell and enable the 'Wrap Text' option from the Home tab to reveal the line breaks.
Does the CHAR(10) formula work if my Excel is in another language?
Excel functions are localized. If your software language is set to anything other than English, you must use the equivalent localized function name for CHAR. For example, in Dutch, you would use =TEKEN(10).
How can I prevent my DYMO label printer from printing each cell line on a separate label?
Certain label printing plugins misinterpret in-cell line breaks as a trigger for a new physical label. To fix this, copy the multi-line cell, paste it into a plain text editor like Notepad to strip formatting, and then copy the resulting text directly into your label software.
