Removing Mulitple Carriage Returns Within a Concantenate Formula
 

Hi,

Having used concantenate to combine cells with a char(10) in between the
cell contents, I now face the problem of multiple carriage returns where
there were empty cells. Is there a formula which will enable me to avoid
this problem or now rectify it?

Thanks,

Jane

Removing Mulitple Carriage Returns Within a Concantenate Formula
 

When you want help with a formula, it is usually a good idea to post that
formula. Here is a framework to do what you want...

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE( CONCATENATE(...),
" ",CHAR(1)),CHAR(10)," "))," ",CHAR(10)),CHAR(1)," ")

Just replace the ellipsis (...) in my formula with the contents of your
CONCATENATE formula.

--
Rick (MVP - Excel)


"Jane" wrote in message
...
Hi,

Having used concantenate to combine cells with a char(10) in between the
cell contents, I now face the problem of multiple carriage returns where
there were empty cells. Is there a formula which will enable me to avoid
this problem or now rectify it?

Thanks,

Jane

Removing Mulitple Carriage Returns Within a Concantenate Formula
 

Suppose you have values in A1,B1,C1

=TRIM(SUBSTITUTE(CONCATENATE(A1," ",B1," ",C1),CHAR(10)," "))

OR

=TRIM(SUBSTITUTE(A1&"."&B1&"."&C1,CHAR(10)," "))
(If you need a separator you can replace the blanks " " with the separator)

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


"Jane" wrote:

Hi,

Having used concantenate to combine cells with a char(10) in between the
cell contents, I now face the problem of multiple carriage returns where
there were empty cells. Is there a formula which will enable me to avoid
this problem or now rectify it?

Thanks,

Jane