Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate Results from a Validated Drop Down List | Excel Discussion (Misc queries) | |||
Drop down box in a cell | New Users to Excel | |||
Refreshing drop down cell values... | Excel Worksheet Functions | |||
changing value of a cell by selecting an item from a drop down list | Excel Worksheet Functions | |||
Can a cell have a drop down list and can also be auto populated | Excel Worksheet Functions |