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?

Type Ctrl-enter in the replace box where you want the line feed to be

Eddie

> *From:* "BK" <nospam@nospam.com>
> *Date:* Tue, 29 Apr 2008 13:23:11 -0500
>
> 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?
>
>
>

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.



"Jeff Hall" <nospam@nospam.com> wrote in message
news:memo.20080429194645.4452D@eonsol.compulink.co .uk...
> Type Ctrl-enter in the replace box where you want the line feed to be
>
> Eddie
>
>> *From:* "BK" <nospam@nospam.com>
>> *Date:* Tue, 29 Apr 2008 13:23:11 -0500
>>
>> 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?
>>
>>
>>
>

CTRL + j

Or Alt + 0010(on numpad)


Gord Dibben MS Excel MVP

On Tue, 29 Apr 2008 14:48:19 -0500, "BK" <nospam@nospam.com> wrote:

>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.
>
>
>
>"Jeff Hall" <nospam@nospam.com> wrote in message
>news:memo.20080429194645.4452D@eonsol.compulink.c o.uk...
>> Type Ctrl-enter in the replace box where you want the line feed to be
>>
>> Eddie
>>
>>> *From:* "BK" <nospam@nospam.com>
>>> *Date:* Tue, 29 Apr 2008 13:23:11 -0500
>>>
>>> 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?
>>>
>>>
>>>
>>
>

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

BK wrote:
>
> 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.
>
> "Jeff Hall" <nospam@nospam.com> wrote in message
> news:memo.20080429194645.4452D@eonsol.compulink.co .uk...
> > Type Ctrl-enter in the replace box where you want the line feed to be
> >
> > Eddie
> >
> >> *From:* "BK" <nospam@nospam.com>
> >> *Date:* Tue, 29 Apr 2008 13:23:11 -0500
> >>
> >> 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?
> >>
> >>
> >>
> >

--

Dave Peterson

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.


"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:hu1f14tt0m305b3jsj2p59m7dsq41d38k1@4ax.com...
> CTRL + j
>
> Or Alt + 0010(on numpad)
>
>
> Gord Dibben MS Excel MVP
>
> On Tue, 29 Apr 2008 14:48:19 -0500, "BK" <nospam@nospam.com> wrote:
>
>>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.
>>
>>
>>
>>"Jeff Hall" <nospam@nospam.com> wrote in message
>>news:memo.20080429194645.4452D@eonsol.compulink. co.uk...
>>> Type Ctrl-enter in the replace box where you want the line feed to be
>>>
>>> Eddie
>>>
>>>> *From:* "BK" <nospam@nospam.com>
>>>> *Date:* Tue, 29 Apr 2008 13:23:11 -0500
>>>>
>>>> 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?
>>>>
>>>>
>>>>
>>>
>>
>

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

Nope!

I replaced "a" with "a<ctrl-enter>b"

First, I typed "a b" in the Replace box
Then I highlighted the space between
Then I pressed Ctrl-Enter
Then I pressed Replace All
Voila!

Eddie

> *From:* "BK" <nospam@nospam.com>
> *Date:* Tue, 29 Apr 2008 14:48:19 -0500
>
> 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.
>
>
>
> "Jeff Hall" <nospam@nospam.com> wrote in message
> news:memo.20080429194645.4452D@eonsol.compulink.co .uk...
> > Type Ctrl-enter in the replace box where you want the line feed to be
> >
> > Eddie
> >
> >> *From:* "BK" <nospam@nospam.com>
> >> *Date:* Tue, 29 Apr 2008 13:23:11 -0500
> >>
> >> 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?
> >>
> >>
> >>
> >
>
>
>

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


"Dave" <Dave@discussions.microsoft.com> wrote in message
news:5D2FD518-3333-433D-9FF2-F7ED3B9DDA4D@microsoft.com...
> 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

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



"Eddie Thrashcort" <nospam@nospam.com> wrote in message
news:memo.20080429232801.2112C@eonsol.compulink.co .uk...
> Nope!
>
> I replaced "a" with "a<ctrl-enter>b"
>
> First, I typed "a b" in the Replace box
> Then I highlighted the space between
> Then I pressed Ctrl-Enter
> Then I pressed Replace All
> Voila!
>
> Eddie
>
>> *From:* "BK" <nospam@nospam.com>
>> *Date:* Tue, 29 Apr 2008 14:48:19 -0500
>>
>> 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.
>>
>>
>>
>> "Jeff Hall" <nospam@nospam.com> wrote in message
>> news:memo.20080429194645.4452D@eonsol.compulink.co .uk...
>> > Type Ctrl-enter in the replace box where you want the line feed to be
>> >
>> > Eddie
>> >
>> >> *From:* "BK" <nospam@nospam.com>
>> >> *Date:* Tue, 29 Apr 2008 13:23:11 -0500
>> >>
>> >> 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?
>> >>
>> >>
>> >>
>> >
>>
>>
>>
>