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.
by Allen Wyatt
(last updated April 5, 2014)
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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
When using an Excel worksheet to store data (such as names and phone numbers), you may need a way to easily look up a ...Discover More
If you use serial numbers that include both letters and numbers, you might wonder how you can increment the numeric ...Discover More
Want to know the letters assigned by Excel to a particular column? Excel normally deals with column numbers, but you can ...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.