Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Maintaining Text Formatting in a Lookup.
by Allen Wyatt
(last updated May 17, 2019)
When you enter text into a worksheet, you can format that text in either of two ways: You can format all the text in the cell, or you can format just some of the text in the cell. Formatting all the text should be familiar to all readers, as it is quite common to make the contents of a cell bold or change its font.
Formatting some of the text within a cell may not be done as often, but it is just as easy to do as formatting the entire cell. All you need to do is, while editing the cell contents, select the text you want to format and then apply the desired formatting properties. For instance, you could make some portion of the text bold or some of it underlined. You can also change the font used by certain characters in a cell, which is often done to insert symbols in a cell or to use foreign-language characters within your regular text.
While formatting can make your information more understandable, and may in fact be required for your particular worksheet, there are some drawbacks. One of the biggest drawbacks is when you use formulaic references to the formatted text. Excel will only copy the bare contents of the referenced cell, not the formatting applied to text within that cell.
As an example, suppose you have a cell that contains Greek letters interspersed within the text of a cell, and you reference that cell in a formula such as the following:
Excel dutifully copies the contents of the cell, returning as the results of the formula, but it does not copy the formatting of the cell being referenced. This behavior is not limited to lookup functions, either; Excel behaves this way even with simple formulas, such as =B7.
There is no way around this drawback, short of writing a rather complicated macro that copies formats as well as content. Macros that copy cell formatting have been covered in past issues of ExcelTips; macros that copy individual character formatting within a cell are complex enough that they are beyond the scope of ExcelTips itself.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2318) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Maintaining Text Formatting in a Lookup.
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!
Operators are used in formulas to instruct Excel what to do to arrive at a result. Not all operators are evaluated in the ...Discover More
Replacing one character in a text value with another character is easy. All you need to do is use the SUBSTITUTE ...Discover More
Teachers often grade on what is affectionately referred to as "the curve." The problem is, it can be a bit difficult to ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.