Merge cells with new line

[BK]

Using Excel 2007

I have a street address in one cell and city/state/zip in another cell. I
want to merge those cells and include the new line code so that they will
print out correctly.

Iused the concatenate function to create one line of text with a unique
sequence of characters when I want the new line code. When using the "find
and replace" function, what do I type into the "replace with" box to get a
new line?

 

[BK]

Nope. First of all it is ALT+ENTER that creates a new line, but I am unable
to put that into the "replace with" box. I think there is some character
code for ALT+ENTER, but I cannot remember it or find it anywhere.

 

[Gord Dibben]

CTRL + j

Or Alt + 0010(on numpad)


Gord Dibben MS Excel MVP

 

[Dave Peterson]

You can use ctrl-j or alt-0010 (use the numbers on the numeric keypad--not the
numbers above the QWERTY keys).

 

[BK]

Seems as though the CTRL+J combination puts an undecipherable character in
the cell rather than a new line.

However, I did play around a little bit. I pasted my cells into Word,
converted table to text, replaced the paragraph mark with a unique character
set (qzzq) and pasted back into Excel. Then I did a find qzzq and replace
with CTRL+J and it worked.

A little convoluted, but at least it beats manually retyping.

 

[Dave]

Hi,
In order for the Ctrl+j to work, you have to format the cell to Wrap Text.
Otherwise you will just get a little box between your data, and no second
line.
Regards - Dave

 

[BK]

TA-DA!!! Forgot to format the cell to wrap text. Thank you so much.

 

[BK]

The trick seems to be to remember to format the cell to wrap text. makes a
big difference.