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