Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Simulating Alt+Enter in a Formula

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.

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.

Related Tips:

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

T Le    20 Oct 2016, 20:39
Thank you very much for this Simulating Alt+Enter in a Formula.
Toeiii    10 Nov 2015, 02:02
Really useful! Thanks :)
rohit    19 Oct 2015, 15:06
I have string value in cell with two line.
I want to color the value after 1st line.

Active
Alert
Discharge

Second two line should be red color.
Srinath    04 Oct 2015, 10:17
Hi friends.. i have a problem in counting Total of few numbers which enterd in one single cell using Alt+Enter. now i need total of those numbers.. please help me..
chaya kanojia    09 Sep 2015, 07:42
its amazing ... i want to high knowledge of excel .. can you help ?????????
Jan    09 Jul 2015, 02:58
Thanks, working perfekt
Clinton    23 Apr 2015, 13:46
Awesome! Thank you!
lucio    04 Mar 2015, 09:16
no Excel 2013, a fórmula é
= A1 & Caract(10) & A2 & Caract(10) & A3
Michael    22 Jan 2015, 14:13
Ok, so am using the Alt + Enter, but am having a unique problem, when I change the second line font color from black to any color, it changes the font size from 10 to 7. Any ideas? No conditional formatting is being used on the cells, FYI
boobalan    04 Jun 2014, 19:25
yes, i agree the function is CHR() not CHAR()
Ben Wit    11 Apr 2014, 03:17
The name of the worksheetfunction you would use depends on the installed language of thhe office version, and is CHAR() for English.

In VBA thhe function,not depending on installed language, CHR()
Scott    09 Apr 2014, 13:44
Thanks, Micky
Michael (Micky) Avidan    08 Apr 2014, 07:12
@Scott,
The result of your believe is wrong.
CHAR is used in sheet formulas, whereas CHR is used in VBA.
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL
Scott    07 Apr 2014, 10:51
I believe that the function is called CHR() not CHAR().
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.