9
=UPPER($B$8)& " " &PROPER($C$8) & "& " & UPPER($B$9)& " "&PROPER($C$9))

I want add a line break to a cell. Current my cell looks like this:

SMITH John & SMITH Linda

But I want to display like this:

SMITH John & 
SMITH Linda

So that there is a line break after the & sign and everything is displayed in one cell. I have been Googling the answer, but I found the answer is usually Alt+Enter, but that only adds a like break to the cell formula, not how it is displayed. Am I doing something wrong?

How can I do that?

0

2 Answers 2

15

Use this formula:

    =UPPER($B$8)& " " & PROPER($C$8) & " &" & CHAR(10) & UPPER($B$9) & " " & PROPER($C$9)

and enable text wrapping in the cell with the formula:

wrap text

or

simpler wrapping of text

EDIT#1:

There are four things at issue here:

  • Text wrapping must be enabled in the formula cell
  • the column must be wide enough to prevent "accidental" line-breaks
  • the CHAR(10) should be inserted where YOU want the line-breaks
  • the row height may need to be adjusted manually

For example

="James" & CHAR(10)  & "Ravenswood"

EDIT#2

Of course if the cell does not contain a formula, but only typed text then using Alt+Enter at the insertion point:

  1. automatically inserts the ASCII-10
  2. automatically turns on Text wrapping
  3. automatically adjusts the row height
7
  • 1
    the space in CHAR(10 ) is redundant?
    – Ben
    Commented May 23, 2015 at 11:34
  • @kuromusha You are correct..........the space has no effect on the formula at all. Commented May 23, 2015 at 11:57
  • 1
    The only important thing seems to be to ensure 'wrap text' is enabled, actually. If 'wrap text' is disabled, Excel strips any linefeed characters and doesn't show them.
    – hBy2Py
    Commented May 23, 2015 at 12:16
  • @Brian.....See my EDIT#1 Commented May 23, 2015 at 12:20
  • Very odd. I'm a lifelong Excel guy. This use of CHAR(10) makes sense, but Excel 2016 64bit is doing nothing with it, no matter the word wrap, height, width, etc. It's treating CHAR(10) as a virtual null. (?!)
    – AMM
    Commented Feb 14, 2017 at 17:53
2

Add CHAR(13) (that works for me, if not works for you try char(10)) AND enable text wrapping in the cell, otherwise new line character won't have effect.

2
  • I don't understand, when I add CHAR10, it just gives me an error.
    – Ben
    Commented May 23, 2015 at 5:33
  • Did you use ` =UPPER($B$8)& " " &PROPER($C$8) & "& " & char(10) & UPPER($B$9)& " "&PROPER($C$9)) ` and get write? Which version of excel do yo use? What is the error? Commented May 23, 2015 at 8:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .