how do you force a new line in a formula in Excel?

[JeffRI]

I'm trying to use the CONCATENATE function but I also want to force a
'return' in the function/formula as if entering data into a cell and using
ALT+ENTER to force a new line. Can this be done?

 

[Tom Hutchins]

You can use CHAR(10) in your Concatenate function, like this:
=CONCATENATE("First line",CHAR(10),"Second line")

You also need to format the cell to allow text wrapping, or the linefeed
character will appear as a little square box.

Hope this helps,

Hutch

 

[JeffRI]

This was awesome!! Thx for the info! That's exactly what I was looking for.

 

[Tyro]

You can also use ="Line1" & CHAR(10) & "Line2"
CONCATENATE is such a long way of saying &.
You must format the cell to wrap text.

Tyro

 

[gamut]

If you need to do this on Mac, there is a silly way to achieve it. Basically, you need to put an actual newline inside quotes. Like so:
=A1 & "
" & B2

To put a newline in there, press Ctrl-Alt-Enter

 

[Andy Spry]

I have a similar challenge, but want to transform the appearance of each line in a multi-line cell and preserve the line ends. Basically I want cell content like this:
NRTMS-URS-REQ-001
NRTMS-URS-REQ-002
NRTMS-URS-REQ-004
NRTMS-URS-REQ-2202
to look like this:
[NRTMS-URS-REQ-001]
[NRTMS-URS-REQ-002]
[NRTMS-URS-REQ-004]
[NRTMS-URS-REQ-2202]

Any ideas?

 

[Andy Spry]

I have a similar challenge, but want to transform the appearance of each line in a multi-line cell and preserve the line ends. Basically I want cell content like this:
NRTMS-URS-REQ-001
NRTMS-URS-REQ-002
NRTMS-URS-REQ-004
NRTMS-URS-REQ-2202
to look like this:
[NRTMS-URS-REQ-001]
[NRTMS-URS-REQ-002]
[NRTMS-URS-REQ-004]
[NRTMS-URS-REQ-2202]

Any ideas?

Worked it out in the end. 3 steps. 1) Use Replace to add the leading [. 2) Use Substitute to replace each line break with a line break preceded by a trailing ]. 3) Use Concatenate to append a trailing ] to final line of text in each cell.