Removing Mulitple Carriage Returns Within a Concantenate Formula

[Jane]

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

 

[Rick Rothstein]

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.

 

[Jacob Skaria]

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