Excel ® 2016 FORMULAS and FUNCTIONS This book is part of Que’s exciting new Content Update Program, which provides automatic content updates for major technology improvements! 4 As Microsoft makes significant updates to Excel 2016, sections of this book will be updated or new sections will be added to match the updates to the software. Insert line breaks manually with Alt + Enter Inserting a line break in Excel is quite easy: Just press Alt + Enter to add a line break inside a cell. This keyboard shortcut works the same way on Windows and the Office 2016 for Mac. Insert line breaks with a formula.

On the Page Layout tab, in the Page Setup group, click Breaks, and then click Insert Page Break. Move a page break On the View menu, click Page Break Preview. Rest the pointer on the page break line until the pointer changes to a, and then drag the page break line to a new location. Mar 25, 2020 Double-click the cell where you want to enter a line break. Type the first part of the text. If the text is already in the cell, place the cursor where you want to break the line. On Windows, hold Alt while pressing the Enter key. In Excel for Mac, hold Control and Option while pressing the Return key. Press Enter to finish up and exit the edit mode.

Some more savvy Excel users know that you can break text onto multiple lines in a cell by pressing Alt+Enter mid entry. Today’s post explores how we can split by line breaks in order to break these types of cell contents into multiple columns.

To start with, let’s set up some simple data:

• In cell A2, type “Text” and press Enter
• In cell A3 type “This” –> Alt + Enter –> “is” –> Alt + Enter –> “text” –> Enter

The result should look like this:

Line Break Excel Cell

And now we’ll go and pull it in to Power Query:

• Select the data –> create new query –> From Table

At this point, you’d certainly be forgiven for thinking that only the first line was pulled in. But if you select the cell, you’ll see in the preview window that all the data is there:

So let’s try and split it up.

• Right click the Text column –> Split Column –> By Delimiter

Unfortunately, there is no line break or carriage return option in the dialog, which means that you’ll need to pick “Custom”, and enter the special character for a Line Feed:

Manual Line Break Excel 2016 Macro

Even worse, with entering this, Power Query is overly aggressive when you click OK. It assumes that this is special text, so escapes it to text, and appends some commands that actually mess you up:

Notice how we have two columns with nothing in the second. What gives there?

Manual Line Break Excel 2016 Mac Download

To correct this code, we need to modify the formula in the formula bar to do two things:

1. Undo the escaping that Power Query did on our #(lf) entry, and
2. Remove the code that is telling which columns to import

So first, we need to replace:

'#(#)(lf)'

with

'#(lf)'

Manual Line Break Excel 2016 Mac Histogram

And second, we need to remove this completely:

,{'Text.1', 'Text.2'}

And the results are much better:

The bad news is that currently it’s a bit painful to do this. The good news is that it can be done, and the better news is that Power Query is constantly being updated. I’m sure it won’t be long before they give us an easier to use/more discoverable mechanism to make this work.

Manual Line Break Excel 2016 Mac How To Show All Non Printing Characters

Should you need them, here are three special characters that you can refer to in Power Query:

Line Break Excel Mac

• Line feed: #(lf)
• Carriage return: #(cr)
• Tab: #(tab)