8
=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
13

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 May 23 '15 at 11:34
  • @kuromusha You are correct..........the space has no effect on the formula at all. – Gary's Student May 23 '15 at 11:57
  • 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 May 23 '15 at 12:16
  • @Brian.....See my EDIT#1 – Gary's Student May 23 '15 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 Feb 14 '17 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 May 23 '15 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? – Máté Juhász May 23 '15 at 8:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.