Using CONCATENATE: May I...

[likewisecc]

insert a carriage return (i.e. Alt-Enter) as a portion of the formula?

Cell C4 contains...

=CONCATENATE(Hal!C49," / ",C60," / ",Hal!C71)

and returns...

257.13 / 311.12 / 366.03

but, I'd like it to return...

257.13
311.12 ("Alt-Enter" plus seven spaces)
366.03 ("Alt-Enter" plus fourteen spaces)

Any ideas?

I must say I wish I could help others on this board as well as some of the advanced members. This board has been the find of a lifetime. I'm very grateful!

 

[BuddieB]

You can use the CHAR(10) command which returns a line break.

=CONCATENATE(C15,CHAR(10),C16,CHAR(10),C17)

 

[Cbrine]

You can use the CHAR(10) command which returns a line break.

=CONCATENATE(C15,CHAR(10),C16,CHAR(10),C17)

Another way
=C15 & Char(10) & C16 & Char(10) & C17

 

[onlyadrafter]

Hello,

You can use

=A1&CHAR(10)&B1&" "&CHAR(10)&C1&" "

or

=CONCATENATE(A2,CHAR(10),B2&" ",CHAR(10),C2&" ")

you will need to WRAP TEXT, as this is the only way I can get them on separate lines.

 

[Russ Kiger]

I've found that the CHAR(10) is not recoginzied IF you attempt to create a CONCATENATE function when the cell is defined as "text". Also, you cannot modify existing CONCATENATE functions in a cell that is defined as text (but you can drag an existing function to an existing text cell as long as you don't try to modify the CONCATENATE string.

Change the cells where you want the CONCATENATE to "number" and things should work as expected.

I hope this helps.

 

[GlendaSings]

After struggling with this for ages, I discovered that it only works if you turn on Wrap Text. Select Format > Cells > Alignment tab > Wrap Text.

 

[tinkywinky]

Hopefully I can solve all the issues around this.

• CHAR(10) is LF (linefeed)
• CHAR(13) is CR (carriage return)
• In Excel, if you do alt+enter inside two double quotes, you get a CHAR(10) LF.

So....

• To display the carriage return in Excel, you need to have:
  • Wrap Text on and
  • Either CHAR(10) or CHAR(13) & CHAR(10). CHAR(13) alone will not work.
• For copy/paste: By default in Windows, a new line is both CRLF together (and not LFCR). Some programs can recognize either CR or LF by itself (like MS Word, Wordpad, or Write). However, notepad requires both a CRLF: CHAR(13) & CHAR(10). So if you want to copy and paste this into a program and you're using windows, your best bet is to use CHAR(13) & CHAR(10). This does not require wrap text on.
  
  One other note is that when you paste into notepad, you'll get double quotes. You can remove these by doing a "replace" replacing the double quote with nothing (assuming you don't have any valid double quotes in which case, you'll need to do some multi step replacing).

Thus use:

=C15 & Char(13) & Char(10) & C16 & Char(13) & Char(10) & C17

or

=CONCATENATE(C15,CHAR(13),CHAR(10),C16,CHAR(13),CHAR(10),C17)

More info on new lines if interested: http://en.wikipedia.org/wiki/Newline

HTH

JM