Character Replacement in Simple Formulas

by Allen Wyatt
(last updated November 15, 2014)

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Using AutoCorrect to Start Macros

As you are typing, AutoCorrect provides a "check" that what you are entering doesn't match some pre-defined error triggers. ...

Discover More

Printing without Headings

One way to use heading styles is to create a story outline. When it comes time to print the story, though, you may not want ...

Discover More

Changing Label Sizes

Information formatted for one type of label may someday need to be printed on a different type of label. Here's some ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

MORE EXCELTIPS (MENU)

Determining a Simple Moving Average

A moving average can be a great way to analyze a series of data points that you've collected over time. Setting up a formula ...

Discover More

Extracting Street Numbers from an Address

Want to know how to move pieces of information contained in one cell into individual cells? This option exists in using ...

Discover More

Deriving Monthly Median Values

When processing huge amounts of data, it can be a challenge to figure out how to derive the aggregate values you need. This ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

This Site

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.

Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share