How To Enter A Line Break In Excel For Mac
With Excel on a Mac a line break is entered by adding the Command (cmd) key to the other two - cmd alt enter. The Command key may be replaced by the Control key (ctrl) in some versions of Excel.
To insert a line break between text we need to use CHAR function. And in today’s post, I’m going to show you exactly 3 different formulas which you can use to have a line break while combining values from different cells. And the best part is all these 3 formulas are simple to use. You can do this by pressing Option Enter at the point you want to insert the line break. It's best that you format the cell (s) as Text to ensure the best results. Mass Insert Line Break (a.k.a Search/Replace) You can do this on a mass basis with Search & Replace.
Sometimes it’s necessary to have more than one line inside a worksheet cell, which is easily done with a line break.
New Line in Excel Cell (MAC)
Add a new line in Excel cell on Mac computers by holding down the Alt key while you press enter. It’s the keyboard shortcut Alt+Enter. In Excel 2008 and 2011 for Mac use Cmd+Option+Enter.
Fig. 1 – Use a Line Break in a Cell with Alt+Enter
A Potential Problem with Using Ctrl+Enter in Excel
The line break is the character code 10. While invisible to the eye in Excel, this character may create problems when exported into a database system by prematurely starting a new record every time it’s encountered.
Using Extra Spaces to Create a Line Break (Not Recommended)
I’ve seen Excel users who don’t know about this shortcut so they try and make the lines wrap inside the cell by providing extra spaces between words. Adding spaces may provide a worksheet cell that looks nice and correct, but an export will also include those extra spaces. (See the formula bar below)
Fig. 2 – Use Spaces to Wrap a Line in a Cell
Another problem with adding extra spaces is that, when the column width changes it can provide an unpredictable alignment. By looking in the formula bar you can see if there’s a proper line break (fig 1) OR extra spaces that might be problematic. (fig 2)
Expanding the Formula Bar in Excel 2007 and 2010
The formula bar typically doesn’t show more than one line. In the two figures above the formula bar has been expanded. You can do this with the keyboard shortcut Ctrl+Shift+U, which you’ll probably never remember, or you can select the drop-down arrow at the right side of the formula bar.
Formula Bar for 2003 and 2008
This keyboard shortcut doesn’t work in Excel 2008 or Excel 2008 for Mac, and there’s no drop-down arrow at the right side of the formula bar. When you encounter a cell with more than one line Excel automatically expands the formula bar. No manual intervention required.
This is why some users are stumped after upgrading to 2007 or 2010 and finding they can’t see the entire cell contents in the formula bar. I know I was. 🙂
When entering text into a cell, sometimes we’d like to insert a line break for betterformatting. A line break is like when you’re in Word and you press Enter tocreate a new line. This can also be called a carriage return. We’ll cover twoways to do this.
- Line breaks in plain text
- Line breaks in cell formulas
One thing is certain, you shouldn’t use two cells for creating the illusion of line breaks.
Adding a Line Break in Plain Text
This one is easy. While you’re editing the text of a cell, you can simply pressAlt+Enter (or Command+Option+Enter for Mac) to add a line break as shownbelow.
NOTE: This will automatically set Wrap Text ON for cell A1.
Let’s take a look at what happens when you turn Wrap Text OFF:
How To Enter A Line Break In Excel For Macs
Notice that in the formula bar the break is kept there, but it is one singleline in cell A1. This is because Excel needs to format the break and Wrap Textis the way to do it. The formula bar is immune to show any formatting, but itwill show you the data you have (in this case, some text, then a line break andsome more text after). It’s just another way to show you that you have a breakin the cell, but it’s not formatted to show it that way.
How To Put A Line Break In Excel Mac
Adding a Line Break in Cell Formulas
To enter a line break in a cell formula, reference the text and concatenate itwith the ampersand (or you can use the CONCATENATE() function) along with thefunction CHAR(10) to insert the break (CHAR(13) on Mac). The CHAR()function takes in an integer and will show a character based on that integer. Please seeMicrosoft’s page on the CHAR() functionfor more info. The 10 (13 for Mac) in this case refers to the character“Line Feed.”
Let’s take B1 and add a formula to combine cells A1 and A2 with a linebreak in between them. The formula to use is:
Windows:
Mac:
Notice this time that Excel does not automatically turn on Wrap Text when weenter the line break as a formula. You have to manually turn it on to get theformatting you want.