Q

Excel tip: Use Alt+Enter to break lines of text in a single cell

Microsoft Excel consultant Bill Jelen, aka 'MrExcel,' shows why Alt+Enter is a more precise alternative to Wrap Text.

You might already know that you can turn on Wrap Text in a cell in order to have a long heading appear in a single

Microsoft Excel cell.

Wrap text

But a better way to wrap text in a single cell is to type Alt+Enter after each line. This allows you to control where the words wrap. For instance, typing Net Income<Alt+Enter>Before<Alt+Enter>Taxes<Enter> will display as:

alt+enter Excel

This is a great trick when it's used for headings. However, it is a horrible technique when an overzealous admin decides to start putting addresses into single cells, as in this figure:

addresses in Excel

There is an easy, albeit obscure solution to fix this. Select the column of data and Data, Text to Columns. Then, choose Delimited.

Excel delimited

Still, this doesn't look right. Only the first line of each cell shows in the preview window.

convert text to columns

But here's the trick to fix it once and for all. From the same dialog box, choose Other. Click into the box next to Other and type Ctrl+j. Instantly, the preview window shows all of the data.

ctrl+j Excel

Click Finish and you will have each line broken out to a new column.

Excel cells

Why Ctrl+j? Great question. There is an ASCII character chart that says the Control Character for a Line Feed is Ctrl+j, yet none of the other control characters from that chart work in the Text to Columns dialog box.

Interestingly, the same Ctrl+j trick works in the Find and Replace dialog box.

Find and replace

Bill Jelen, aka MrExcel, has been a Microsoft Excel consultant for over twenty-five years. Read more of his expert Excel tips and tricks at his website, MrExcel.com.

This was first published in April 2014

Dig deeper on ERP System

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

1 comment

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchManufacturingERP

SearchSAP

SearchOracle

SearchBusinessAnalytics

SearchContentManagement

SearchDataManagement

SearchCRM

Close