Concatenate into 1 cell and drop down lines?
 

Here is my formula:
=CONCATENATE
(A4,";"," ",B4," ",C4," ",D4," ",E4," ",F4,","," ",G4," ",H4," ",I4," ",J4,"
",K4," ",L4," ",M4)

Returns:
Aukes; Jeff & Andrea 6812 321st Lane NE Apt #310 Stacy MN, 55079 (651)
462-5716 (651) 462-2803 Michael Ashley Member - Active 5/17/06

I want it to appear on separate lines (but in one cell) as showen below:

Aukes; Jeff & Andrea [A4, B4]
6812 321st Lane NE [C4]
Apt #310 [D4]
Stacy MN, 55079 [E4, F4, G4]
(651) 462-5716 [H4]
(651) 462-2803 [I4]
Michael [J4]
Ashley [K4]
Member - Active [L4]
5/17/06 [M4]

I also want "Aukes;" to be bold. That would be [A4].
Is this possible?

Concatenate into 1 cell and drop down lines?
 

Try in say, N4:
=CONCATENATE(A4,";"," ",B4,CHAR(10),C4,CHAR(10),D4,CHAR(10),E4," ",F4,",","
",G4,CHAR(10),H4,CHAR(10),I4,CHAR(10),J4,CHAR(10), K4,CHAR(10),L4,CHAR(10),TEXT(M4,"m/dd/yy"))

Then format N4 to wrap text
(via: Format Cells Alignment tab Check Wrap Text OK)
Copy N4 down

I also want "Aukes;" to be bold. That would be [A4].

AFAIK, not possible ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jeff" wrote:
Here is my formula:
=CONCATENATE
(A4,";"," ",B4," ",C4," ",D4," ",E4," ",F4,","," ",G4," ",H4," ",I4," ",J4,"
",K4," ",L4," ",M4)

Returns:
Aukes; Jeff & Andrea 6812 321st Lane NE Apt #310 Stacy MN, 55079 (651)
462-5716 (651) 462-2803 Michael Ashley Member - Active 5/17/06

I want it to appear on separate lines (but in one cell) as showen below:

Aukes; Jeff & Andrea [A4, B4]
6812 321st Lane NE [C4]
Apt #310 [D4]
Stacy MN, 55079 [E4, F4, G4]
(651) 462-5716 [H4]
(651) 462-2803 [I4]
Michael [J4]
Ashley [K4]
Member - Active [L4]
5/17/06 [M4]

I also want "Aukes;" to be bold. That would be [A4].
Is this possible?

Concatenate into 1 cell and drop down lines?
 

I already figured how to do most of this by myself. I was originally putting
"alt+enter" in the wrong place in the formula.

But I still can't figure out how to make "Aukes;" bold.

Another question: Cells D4, I4, J4, K4 may sometimes be empty. When they are
empty - I prefer those lines to not appear.

Example; instead of this. . .:
Aukes; Jeff & Andrea [A4, B4]
6812 321st Lane NE [C4]

Stacy MN, 55079 [E4, F4, G4]
(651) 462-5716 [H4]



Member - Active
5/17/06 [M4]

.. . .I prefer this:
Aukes; Jeff & Andrea [A4, B4]
6812 321st Lane NE [C4]
Stacy MN, 55079 [E4, F4, G4]
(651) 462-5716 [H4]
Member - Active
5/17/06 [M4]

Is this possible?
I know I won't be able to figure this one out by myself.

Concatenate into 1 cell and drop down lines?
 

Hi Max,
Your formula worked good, but I still have probems. See my post below (or
above, I don't know were this will be when I hit Post.)

By the way, what does "AFAIK" mean? Are you saying that it is not possible
to make some characters bold?

Thanks for you help.

"Max" wrote:

Try in say, N4:
=CONCATENATE(A4,";"," ",B4,CHAR(10),C4,CHAR(10),D4,CHAR(10),E4," ",F4,",","
",G4,CHAR(10),H4,CHAR(10),I4,CHAR(10),J4,CHAR(10), K4,CHAR(10),L4,CHAR(10),TEXT(M4,"m/dd/yy"))

Then format N4 to wrap text
(via: Format Cells Alignment tab Check Wrap Text OK)
Copy N4 down

I also want "Aukes;" to be bold. That would be [A4].

AFAIK, not possible ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jeff" wrote:
Here is my formula:
=CONCATENATE
(A4,";"," ",B4," ",C4," ",D4," ",E4," ",F4,","," ",G4," ",H4," ",I4," ",J4,"
",K4," ",L4," ",M4)

Returns:
Aukes; Jeff & Andrea 6812 321st Lane NE Apt #310 Stacy MN, 55079 (651)
462-5716 (651) 462-2803 Michael Ashley Member - Active 5/17/06

I want it to appear on separate lines (but in one cell) as showen below:

Aukes; Jeff & Andrea [A4, B4]
6812 321st Lane NE [C4]
Apt #310 [D4]
Stacy MN, 55079 [E4, F4, G4]
(651) 462-5716 [H4]
(651) 462-2803 [I4]
Michael [J4]
Ashley [K4]
Member - Active [L4]
5/17/06 [M4]

I also want "Aukes;" to be bold. That would be [A4].
Is this possible?

Concatenate into 1 cell and drop down lines?
 

"Jeff" wrote:
Your formula worked good, but I still have probems. See my post ..

Another question: Cells D4, I4, J4, K4 may sometimes be empty. When they are
empty - I prefer those lines to not appear...

One way would be to IF front-check all the said cells, then apply the
earlier expression with the parts referring to the said cells removed from it
as value_if_TRUE, then with the original expression applied as the
value_if_FALSE.

Eg, in N4, copied down [with N4 formatted to wrap text, as before]:
=IF(AND(D4="",I4="",J4="",K4=""),CONCATENATE(A4,"; ","
",B4,CHAR(10),C4,CHAR(10),E4," ",F4,",","
",G4,CHAR(10),H4,CHAR(10),L4,CHAR(10),TEXT(M4, "m/dd/yy")),CONCATENATE(A4,";","
",B4,CHAR(10),C4,CHAR(10),D4,CHAR(10),E4," ",F4,",","
",G4,CHAR(10),H4,CHAR(10),I4,CHAR(10),J4,CHAR(10), K4,CHAR(10),L4,CHAR(10),TEXT(M4,"m/dd/yy")))

By the way, what does "AFAIK" mean?

AFAIK = As far as I know

Are you saying that it is not possible
to make some characters bold?

Yes. Formulas cannot return formatting (even more "partial" formatting)

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---