Removing Mulitple Carriage Returns Within a Concantenate Formula

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
 
R

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.
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top