This formula "glues together" the pieces of text that appear in B4, C4, and D4 using the ampersand (&) which is the concatenation operator in Excel.
In between each piece of text, the CHAR function appears with the character code 10. The character code for a line break in Excel varies depending on the platform. On Windows, the code is 10 and on a Mac it's 13.
The result of the concatenation is text with line breaks:
1301 Robinson Court¬
Saginaw, MI 48607
Note: make sure you have text wrap enabled on cells that contain line breaks.
To count total lines in a cell, you can use a formula based on the LEN, SUBSTITUTE, and CHAR functions. In the example, we are using this formula: = LEN ( B5 ) - LEN ( SUBSTITUTE ( B5 , CHAR ( 10 ), "" )) + 1 How the formula works First,...
To add a line break taking into account the current OS (Mac or Windows), you can use the INFO function to test the system and then return the correct break character — CHAR(10) for Windows, CHAR(13) for Mac. How this formula works The character used...
The Excel CHAR function returns a character when given a valid character code. CHAR can be used to specify characters that are hard to enter in a formula. For example, CHAR(10) returns a line break on Windows, and CHAR(13) returns a line break on...
In this video, we demo how to join different values together in one cell with line breaks. Also includes a clever way to add spaces and commas without using double quotes.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.