Share via

carriage return in a formula

Anonymous
2011-03-02T11:41:17+00:00

Excel 2007

I know you can use Alt+Return to add a carriage return (new line) in a cell but how do you do this in the middle of a formula?

Say I have a concatenate formula, and I have ,&"test"&, in the formula, what can I use instead of "test" to force a carriage return as part of the formula result?

Many thanks

DeanH

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2011-03-02T11:54:57+00:00

    DeanH wrote:

    I know you can use Alt+Return to add a carriage return (new line) in a cell but how do you do this in the middle of a formula?

    Try:

    ="new"&CHAR(10)&"line"

    formatted with Wrap Text set in the Alignment tab.

    Caveat:  There are many operations that will cause this to revert to appearing as "new()line", where I use "()" to represent the square box that represents nonprinting characters.  When that happens, you will need to re-edit the cell by selecting the cell, pressing F2, then Enter.

    20+ people found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-03-02T16:21:16+00:00

    DeanH wrote:

    Is there a list somewhere of the allowed &***& components, such as this Char(10)?

    CHAR(10) is a function that returns a single character whose ASCII code is 10.  There are 256 ASCII codes; but Excel CHAR does not permit zero.  For a list of ASCII codes, see http://www.asciitable.com.

    As for the "&" operator....  "op1 & op2" concatenates two operands to make a string.  op1 and op2 can be just about anything -- cell references, quoted strings and string, logical and numerical expressions.

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2011-03-02T12:13:30+00:00

    Many thanks, this works well (once the format is wrapped).

    Also works in 2003, so probably in all other versions as well.

    Cheers

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2011-03-02T12:27:17+00:00

    Hi joeu2004

    Is there a list somewhere of the allowed &***& components, such as this Char(10)?

    0 comments