Concantenation Problem
 

Columns A through E usually contain a "paragraph" of text. In order to
combine, but separate, the paragraphs in column F with line breaks, I have
written the following formula:

=A1&CHAR(10)&B1&CHAR(10)&C1&CHAR(10)&D1&CHAR(10)&E 1

However, for a given row, some of the columns (A - E) may not contain any
text. So I'm trying to figure out how to modify my formula so that CHAR(10)
is appended to only those columns that contain text. In other words, how do
I avoid having column F display unnecessary (i.e., extra) line breaks?

I would prefer to solve this problem without having to write a UDF or macro.
Any help would be greatly appreciated.
Thanks,
Bob

Concantenation Problem
 

=IF(A1="","",A1&CHAR(10))&IF(B1="","",B1&CHAR(10)) &IF(C1="","",C1&CHAR(10))&IF(D1="","",D1&CHAR(10)) &IF(E1="","",E1&CHAR(10))

or
Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

=SUBSTITUTE(TRIM(MCONCAT(IF(A1:E1<"",A1:E1,"")&" "))," ",CHAR(10))

ctrl+shift+enter, not just enter


"Bob" wrote:

Columns A through E usually contain a "paragraph" of text. In order to
combine, but separate, the paragraphs in column F with line breaks, I have
written the following formula:

=A1&CHAR(10)&B1&CHAR(10)&C1&CHAR(10)&D1&CHAR(10)&E 1

However, for a given row, some of the columns (A - E) may not contain any
text. So I'm trying to figure out how to modify my formula so that CHAR(10)
is appended to only those columns that contain text. In other words, how do
I avoid having column F display unnecessary (i.e., extra) line breaks?

I would prefer to solve this problem without having to write a UDF or macro.
Any help would be greatly appreciated.
Thanks,
Bob

Concantenation Problem
 

Thanks for your help! I really appreciate it.
Bob

"Teethless mama" wrote:

=IF(A1="","",A1&CHAR(10))&IF(B1="","",B1&CHAR(10)) &IF(C1="","",C1&CHAR(10))&IF(D1="","",D1&CHAR(10)) &IF(E1="","",E1&CHAR(10))

or
Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

=SUBSTITUTE(TRIM(MCONCAT(IF(A1:E1<"",A1:E1,"")&" "))," ",CHAR(10))

ctrl+shift+enter, not just enter


"Bob" wrote:

Columns A through E usually contain a "paragraph" of text. In order to
combine, but separate, the paragraphs in column F with line breaks, I have
written the following formula:

=A1&CHAR(10)&B1&CHAR(10)&C1&CHAR(10)&D1&CHAR(10)&E 1

However, for a given row, some of the columns (A - E) may not contain any
text. So I'm trying to figure out how to modify my formula so that CHAR(10)
is appended to only those columns that contain text. In other words, how do
I avoid having column F display unnecessary (i.e., extra) line breaks?

I would prefer to solve this problem without having to write a UDF or macro.
Any help would be greatly appreciated.
Thanks,
Bob

Concantenation Problem
 

You're Welcome!


"Bob" wrote:

Thanks for your help! I really appreciate it.
Bob

"Teethless mama" wrote:

=IF(A1="","",A1&CHAR(10))&IF(B1="","",B1&CHAR(10)) &IF(C1="","",C1&CHAR(10))&IF(D1="","",D1&CHAR(10)) &IF(E1="","",E1&CHAR(10))

or
Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

=SUBSTITUTE(TRIM(MCONCAT(IF(A1:E1<"",A1:E1,"")&" "))," ",CHAR(10))

ctrl+shift+enter, not just enter


"Bob" wrote:

Columns A through E usually contain a "paragraph" of text. In order to
combine, but separate, the paragraphs in column F with line breaks, I have
written the following formula:

=A1&CHAR(10)&B1&CHAR(10)&C1&CHAR(10)&D1&CHAR(10)&E 1

However, for a given row, some of the columns (A - E) may not contain any
text. So I'm trying to figure out how to modify my formula so that CHAR(10)
is appended to only those columns that contain text. In other words, how do
I avoid having column F display unnecessary (i.e., extra) line breaks?

I would prefer to solve this problem without having to write a UDF or macro.
Any help would be greatly appreciated.
Thanks,
Bob