Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Simulating Alt+Enter in a Formula.
by Allen Wyatt
(last updated July 9, 2019)
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 the small icon at the lower-right of the Alignment group on the Home tab of the ribbon). On the Alignment tab select the Wrap Text check box.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12568) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Simulating Alt+Enter in a Formula.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
When you are working with sequenced values in a list, you’ll often want to take some action based on the top X or ...Discover More
You can easily sum a series of values in Excel, but it is not so easy to sum the absolute values of each value in a ...Discover More
It's easy to use filtering to hide rows based on the value in a cell, but how do you hide rows based on the values in two ...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 ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.