Add line break based on OS

In older versions of Excel (before Excel 2016?), the character used for line breaks is different depending on whether Excel is running on a Mac or Windows computer: On Windows Excel, the line break character is ASCII 10. In older versions of Excel on a Mac, the line break character is ASCII 13. These are invisible characters and therefore difficult to enter directly into a formula. The standard way to insert them in a formula is to use the CHAR function like this:

CHAR(10) // line break in Win Excel
CHAR(13) // line break in Mac Excel

Because the line break varies by platform in older versions of Excel, it is tricky to write a single formula that will work as expected on both platforms. One solution is to use the INFO function to test the current environment and then set a value for a line break that is conditional on the platform. In the worksheet shown, we do this by first naming cell C3 "break". Then, in the same cell, we enter the following formula:

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

Now we can use the word break like a variable in a formula. If Excel is running on a Mac, break will equal CHAR(13), if not, break will equal CHAR(10). In column E, we can then 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: to see the line break take effect, you will need to enable text wrap.