[AfricanTigress]

The formula below works but I would like to omit lines that are blank.

i.e.
=CONCATENATE(I15,CHAR(10),J15,CHAR(10),K15,CHAR(10 ),L15,CHAR(10),M15,CHAR(10),N15,CHAR(10),O15)

Say line K15, L15 and M15 had no data like this :

I 227 Finney Lane
J Heald Green
K
L
M
N Cheadle
O SK8 3QB

How can I tell it to omit the blank lines, K,L,M so the address is without
blanks?

Your help would be much appreciated.

Thanx

[Ashish Mathur[_2_]]

Hi,

Please download the Excel morefunc5 add-in from
http://download.cnet.com/Morefunc/30...-10423159.html and after
installing it, you may use the following array formula (Ctrl+Shift+Enter)

=SUBSTITUTE(MCONCAT(IF((I6:I10<""),I6:I10)),"FALS E","")

where I6:I10 is the range which holds the data and blanks

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"AfricanTigress" wrote in message
...
The formula below works but I would like to omit lines that are blank.

i.e.
=CONCATENATE(I15,CHAR(10),J15,CHAR(10),K15,CHAR(10 ),L15,CHAR(10),M15,CHAR(10),N15,CHAR(10),O15)

Say line K15, L15 and M15 had no data like this :

I 227 Finney Lane
J Heald Green
K
L
M
N Cheadle
O SK8 3QB

How can I tell it to omit the blank lines, K,L,M so the address is without
blanks?

Your help would be much appreciated.

Thanx

[Shane Devenshire[_2_]]

Hi,

Somewhat of a pain:

=CONCATENATE(IF(I15="","",I15),IF(I15="","",CHAR(1 0)),IF(J15="","",J15),IF(J15="","",CHAR(10)),IF(K1 5="","",K15),IF(K15="","",CHAR(10)),IF(L15="","",L 15),IF(L15="","",CHAR(10)),IF(M15="","",M15),IF(M1 5="","",CHAR(10)),IF(N15="","",N15),IF(N15="","",C HAR(10)),IF(O15="","",O15))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"AfricanTigress" wrote:

The formula below works but I would like to omit lines that are blank.

i.e.
=CONCATENATE(I15,CHAR(10),J15,CHAR(10),K15,CHAR(10 ),L15,CHAR(10),M15,CHAR(10),N15,CHAR(10),O15)

Say line K15, L15 and M15 had no data like this :

I 227 Finney Lane
J Heald Green
K
L
M
N Cheadle
O SK8 3QB

How can I tell it to omit the blank lines, K,L,M so the address is without
blanks?

Your help would be much appreciated.

Thanx

[Shane Devenshire[_2_]]

You can shorten my previous suggestion by:

=CONCATENATE(IF(I15="",,I15),IF(I15="",,CHAR(10)), IF(J15="",,J15),IF(J15="",,CHAR(10)),IF(K15="",,K1 5),IF(K15="",,CHAR(10)),IF(L15="",,L15),IF(L15="", ,CHAR(10)),IF(M15="",,M15),IF(M15="",,CHAR(10)),IF (N15="",,N15),IF(N15="",,CHAR(10)),IF(O15="",,O15) )

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"AfricanTigress" wrote:

The formula below works but I would like to omit lines that are blank.

i.e.
=CONCATENATE(I15,CHAR(10),J15,CHAR(10),K15,CHAR(10 ),L15,CHAR(10),M15,CHAR(10),N15,CHAR(10),O15)

Say line K15, L15 and M15 had no data like this :

I 227 Finney Lane
J Heald Green
K
L
M
N Cheadle
O SK8 3QB

How can I tell it to omit the blank lines, K,L,M so the address is without
blanks?

Your help would be much appreciated.

Thanx

[Jacob Skaria]

All in one line

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(I15," ","|")&CHAR(32)&
SUBSTITUTE(J15," ","|")&CHAR(32)&
SUBSTITUTE(K15," ","|")&CHAR(32)&
SUBSTITUTE(L15," ","|")&CHAR(32)&
SUBSTITUTE(M15," ","|")&CHAR(32)&
SUBSTITUTE(N15," ","|")&CHAR(32)&
SUBSTITUTE(O15," ","|")),CHAR(32),CHAR(10)),"|",CHAR(32))

If this post helps click Yes
---------------
Jacob Skaria


"AfricanTigress" wrote:

The formula below works but I would like to omit lines that are blank.

i.e.
=CONCATENATE(I15,CHAR(10),J15,CHAR(10),K15,CHAR(10 ),L15,CHAR(10),M15,CHAR(10),N15,CHAR(10),O15)

Say line K15, L15 and M15 had no data like this :

I 227 Finney Lane
J Heald Green
K
L
M
N Cheadle
O SK8 3QB

How can I tell it to omit the blank lines, K,L,M so the address is without
blanks?

Your help would be much appreciated.

Thanx

[Jacob Skaria]

A shorter version

=SUBSTITUTE(SUBSTITUTE(I15&CHAR(10)&J15&
CHAR(10)&K15&CHAR(10)&L15&CHAR(10)&M15&
CHAR(10)&N15&CHAR(10)&O15,REPT(CHAR(10),3),CHAR(10 )),
REPT(CHAR(10),2),CHAR(10))

If this post helps click Yes
---------------
Jacob Skaria


"AfricanTigress" wrote:

The formula below works but I would like to omit lines that are blank.

i.e.
=CONCATENATE(I15,CHAR(10),J15,CHAR(10),K15,CHAR(10 ),L15,CHAR(10),M15,CHAR(10),N15,CHAR(10),O15)

Say line K15, L15 and M15 had no data like this :

I 227 Finney Lane
J Heald Green
K
L
M
N Cheadle
O SK8 3QB

How can I tell it to omit the blank lines, K,L,M so the address is without
blanks?

Your help would be much appreciated.

Thanx