Exceljet

Quick, clean, and to the point

Add line break based on OS

Excel formula: Add line break based on OS
Generic formula 
=IF(INFO("system")="mac",CHAR(13),CHAR(10))
Explanation 

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 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 solution is to use the INFO function to test the current environment, then adjust the line break character accordingly. We do this by adding this formula to C3:

=IF(INFO("system")="mac",CHAR(13),CHAR(10))

Then we we name C3 "break" so we can use the word break like a variable later. If Excel is running on a Mac, break will equal CHAR(13), if not, break will equal CHAR(10).

In column E, we concatenate the address information that appears in B, C, and D with a formula like this:

=B6&break&C6&break&D6

The result of the concatenation is text with line breaks:

Traci Brown¬
1301 Robinson Court¬
Saginaw, MI 48607

Note: make sure you have text wrap enabled on cells that contain line breaks.

Author 
Dave Bruns

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.