Written by Allen Wyatt (last updated November 25, 2023)
This tip applies to Excel 97, 2000, 2002, and 2003
Want to try an experiment? Enter something in a cell, but make sure that you start a new line somewhere within the cell. For instance, enter 123 then press Alt+Enter and press 456. You should see two lines within the cell, with three digits on each line. Now, in another cell, put a simple formula that references the first cell, as in =A7.
What happened after you entered the formula? The results of the formula should not look the same as what you typed in the first cell. Instead of being on two lines, the contents should be on one line, separated by a small, rectangular box. (See Figure 1.)
Figure 1. Odd characters: replaced in the formula?
It is interesting to note that this experiment works because when you press Alt+Enter to put in your first cell's value, Excel automatically turns on text wrapping for the cell. You can verify this by selecting the cell and choosing Format | Cells | Alignment tab (pay attention to the Wrap Text check box). When you used the formula, however, the cell into which the results were copied did not have this attribute turned on, so the character created by Alt+Enter was displayed as a text character instead of controlling wrapping.
If you turn on text wrapping for the target cell, then the text in the formula's cell will display on two lines, just like you would expect. Conversely, if you turn off text wrapping in the original cell, then the text folds back up to one line and the small, rectangular box appears.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3206) applies to Microsoft Excel 97, 2000, 2002, and 2003.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
If you use serial numbers that include both letters and numbers, you might wonder how you can increment the numeric ...
Discover MoreAn easy way to create a name for a formula or constant value. The name can then be used in other formulas or for ...
Discover MoreWant to sum the values in the same cell on a range of worksheets? It's not as easy as summing a range on the same ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments