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