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.
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:
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:
There is an easy, albeit obscure solution to fix this. Select the column of data and Data, Text to Columns. Then, choose Delimited.
Still, this doesn't look right. Only the first line of each cell shows in the preview window.
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.
Click Finish and you will have each line broken out to a new column.
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.
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.