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 last published in April 2014

Dig Deeper on ERP System

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Join the conversation

3 comments

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

These ideas are great except they don't always work on a Mac using Excel 2011 version 14.3.6 (130613). I tried this using each of the modifier keys to no avail. In fact I have a lot of problems with Excel on the Mac. For instance, if I drag data down from one cell to the next in a non-formula cell, any formulas in adjacent cells in the original line get changed usually producing an error. If I copy data containing a date cell in one document to another Excel document containing a formatted date cell it will ask if I want to convert the date. Both documents were created in the same version of Excel. I emailed this to MS with explicit examples asking them to fix it when 2011 first came out and they have yet to do so.
Cancel
I also find Alt+Enter trick very handy.
But the example how to convert multi-line data cells in multiple cells is the most useful. Bookmarking.
Cancel
I like this one. Will help me tidy up some worksheets. The wrap text sometimes leaves a little to be desired.
Cancel

-ADS BY GOOGLE

SearchManufacturingERP

SearchSAP

SearchOracle

SearchBusinessAnalytics

SearchContentManagement

SearchDataManagement

SearchCRM

Close