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: Simulating Alt+Enter in a Formula.
Written by Allen Wyatt (last updated May 28, 2022)
This tip applies to Excel 97, 2000, 2002, and 2003
Kirk has a formula that concatenates text values: =A1 & A2 & A3. He is looking for a way to simulate the pressing of Alt+Enter between each of the concatenated values. There are two ways that this can be approached, and both end up with the same results.
The first method is to simply press Alt+Enter between the values as you are entering the formula. For instance, consider the following formula:
=A1 & "[ae]" & A2 & "[ae]" & A3
In this instance, every place that you see [ae] you would actually press Alt+Enter. Thus, you would end up with a formula that looked like this just before entering it:
=A1 & " " & A2 & " " & A3
If you find entering this type of formula distracting, you can always use the actual character code that Excel does whenever you press Alt+Enter. The following formula shows this approach:
=A1 & CHAR(10) & A2 & CHAR(10) & A3
The CHAR(10) inserts a line feed character, which is the same as is done by Excel when you press Alt+Enter.
If, after entering your formula, you don't see the results on separate lines, it is because you don't have wrapping turned on for the cell. Instead you'll see a small square box where the line feed character is located. To see the results of the formula on separate lines, display the Format Cells dialog box (click Format | Cells). On the Alignment tab select the Wrap Text check box.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2788) 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: Simulating Alt+Enter in a Formula.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Enter a formula (starting with an equal sign) and you may be surprised if Excel doesn't calculate the formula. Here's a ...
Discover MoreWant to figure out how far it is between two points on the globe? If you know the points by latitude and longitude, you ...
Discover MoreNeed to count the number of W (win) or L (loss) characters in a range of cells? You can develop a number of formulaic ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-01-16 10:14:23
zone
Thank you very much.
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.
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:
Comments