Is is possible to add a hard return to a merge cell formula?

N

Nmo11

I am trying to merge multiple cells from one worksheet to another. I need a
hard return between a few items so they all line up on the left. Is this
possible or should i try something else?
 
D

Dave Peterson

Are you using a formula?

='sheet1'!a1&char(10)&'sheet2'!b2&char(10)&'sheet3'!c3

Make sure you format the cell for wraptext:
format|cells|alignment tab.
(else you'll see little boxes where the char(10)'s will be)
 
C

CyberTaz

Not at all clear on what your intended result is, but you cannot _merge_
cells on separate worksheets.

If you are trying to label rows the same on two different sheets, hold Shift
while you click the Tab for the second sheet. Then type each of your labels
in a separate cell, pressing Enter as you go. In order to deselect the
additional sheet(s), click any Tab except the one with the *bold* name.

If I missed my guess, please post back with more specific detail on what you
are trying to do.

HTH |:>)
 
N

nmo11

I don't know if I am asking the impossible. I am getting a spread sheet with
client information. i want to be able to take this information and reformat
what I need into a single cell. I am using a formula (&" "&) which allows me
to merge the needed cells but this doesn't allow me to make a hard return in
between certain words. I have the wrap text on but it doesn't allow me to
seperate certain items so they are aligned on the left hand side.

I want it to look like this in one cell:

Mr. Joe Snow
"Joe"
IMG
First Time Winner
Tampa, FL

Is this possible?
 
D

David McRitchie

you can concatenate a CHAR(10) which is actually a line feed,
which is what Excel pays attention to.

in Excel
=A1 & CHAR(10) & B1
and you must turn on Wrap cells under Format, cells, alignment, wrap

also in Excel you can use Alt+Enter when typing and it
will turn on the wrap cell attribute for you.

in VBA
=string1 & CHR(10) & string2
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

nmo11 said:
I don't know if I am asking the impossible. I am getting a spread sheet with
client information. i want to be able to take this information and reformat
what I need into a single cell. I am using a formula (&" "&) which allows me
to merge the needed cells but this doesn't allow me to make a hard return in
between certain words. I have the wrap text on but it doesn't allow me to
separate [correct spelling is separate] certain items so they are aligned on the left hand side.

I want it to look like this in one cell:

Mr. Joe Snow
"Joe"
IMG
First Time Winner
Tampa, FL

Is this possible?

CyberTaz said:
Not at all clear on what your intended result is, but you cannot _merge_
cells on separate worksheets.

If you are trying to label rows the same on two different sheets, hold Shift
while you click the Tab for the second sheet. Then type each of your labels
in a separate cell, pressing Enter as you go. In order to deselect the
additional sheet(s), click any Tab except the one with the *bold* name.

If I missed my guess, please post back with more specific detail on what you
are trying to do.

HTH |:>)
Click to expand...
Click to expand...
 
Top