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 Wrap Text enabled on cells that contain line breaks.
In Excel 365, both Win and Mac versions of Excel use CHAR(10) as a line break.
First, the LEN function counts total characters in the cell B5. Next SUBSTITUTE removes all "line returns" from the text in B5 by looking for CHAR(10) which is the character code for the return character in Windows. LEN returns the result inside of...
The character used for line breaks is different depending on whether Excel is running on Mac or Windows: CHAR(10) for Windows, CHAR(13) for Mac. This makes it tricky to write a single formula that will work as expected on both platforms. The...
To include double quotes inside a formula, you can use additional double quotes as escape characters . By escaping a character, you are telling Excel to treat the " character as literal text. You'll also need to include double quotes wherever you...
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, and can be used to add a line break to text in...
In this video we demo how to join different values together in one cell with line breaks. This 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.