Written by Allen Wyatt (last updated May 21, 2019)
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.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
When processing data in a worksheet, you may have a need to know what the smallest (lowest) even value in a range is. You ...
Discover MoreThe FIND and SEARCH functions are great for finding the initial occurrence of a character in a text string, but what if ...
Discover MoreWhen editing information in a cell, you may need to know the result of a portion of your formula. The shortcut described ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2014-11-15 06:26:34
Ray Austin
I do NOT see a small black box in the formula cell, all I see is 123456, so one cannot see that the text is in two parts.
However turning on Text Wrapping does put it onto 2 lines.
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 © 2023 Sharon Parq Associates, Inc.
Comments