Removing Mulitple Carriage Returns Within a Concantenate Formula

  • Thread starter Thread starter Jane
  • Start date Start date
J

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
 
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.
 
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
 
Back
Top