Concatenate problem

[harwookf]

Excel 2007. I have added the formula shown below to concatenate 26 cells on
a different worksheet, with each one showing on a separate line. However, if
there is a blank cell, it shows as a blank line when really I don't want this
to be included. Is there an easy way to solve this, perhaps with an easier
formula?

Also, I have row to set to auto-height, but yet it doesn't seem to
automatically alter when required. Is there a way to solve this?

Thanks in advance.

=CONCATENATE('Input Notes Sheet'!C2,CHAR(10),'Input Notes
Sheet'!C3,CHAR(10),'Input Notes Sheet'!C4,CHAR(10),'Input Notes
Sheet'!C5,CHAR(10),'Input Notes Sheet'!C6,CHAR(10),'Input Notes
Sheet'!C7,CHAR(10),'Input Notes Sheet'!C8,CHAR(10),'Input Notes
Sheet'!C9,CHAR(10),'Input Notes Sheet'!C10,CHAR(10),'Input Notes
Sheet'!C11,CHAR(10),'Input Notes Sheet'!C12,CHAR(10),'Input Notes
Sheet'!C13,CHAR(10),'Input Notes Sheet'!C14,CHAR(10),'Input Notes
Sheet'!C15,CHAR(10),'Input Notes Sheet'!C16,CHAR(10),'Input Notes
Sheet'!C17,CHAR(10),'Input Notes Sheet'!C18,CHAR(10),'Input Notes
Sheet'!C19,CHAR(10),'Input Notes Sheet'!C20,CHAR(10),'Input Notes
Sheet'!C21,CHAR(10),'Input Notes Sheet'!C22,CHAR(10),'Input Notes
Sheet'!C23,CHAR(10),'Input Notes Sheet'!C24,CHAR(10),'Input Notes
Sheet'!C25,CHAR(10),'Input Notes Sheet'!C26,CHAR(10))

 

[Jacob Skaria]

There is no shortcut other than to use a macro/UDF..There can be a easier
solution depending on the data you have in these cells...is that text with
more than one words or what??? The below should take care of the blank cells
issue....Try and feedback

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
CONCATENATE('Input Notes Sheet'!C2,CHAR(10),'Input Notes
Sheet'!C3,CHAR(10),'Input Notes Sheet'!C4,CHAR(10),'Input Notes
Sheet'!C5,CHAR(10),'Input Notes Sheet'!C6,CHAR(10),'Input Notes
Sheet'!C7,CHAR(10),'Input Notes Sheet'!C8,CHAR(10),'Input Notes
Sheet'!C9,CHAR(10),'Input Notes Sheet'!C10,CHAR(10),'Input Notes
Sheet'!C11,CHAR(10),'Input Notes Sheet'!C12,CHAR(10),'Input Notes
Sheet'!C13,CHAR(10),'Input Notes Sheet'!C14,CHAR(10),'Input Notes
Sheet'!C15,CHAR(10),'Input Notes Sheet'!C16,CHAR(10),'Input Notes
Sheet'!C17,CHAR(10),'Input Notes Sheet'!C18,CHAR(10),'Input Notes
Sheet'!C19,CHAR(10),'Input Notes Sheet'!C20,CHAR(10),'Input Notes
Sheet'!C21,CHAR(10),'Input Notes Sheet'!C22,CHAR(10),'Input Notes
Sheet'!C23,CHAR(10),'Input Notes Sheet'!C24,CHAR(10),'Input Notes
Sheet'!C25,CHAR(10),'Input Notes Sheet'!C26,CHAR(10))
,REPT(CHAR(10),3),CHAR(10)),REPT(CHAR(10),2),CHAR(10)),REPT(CHAR(10),3),CHAR(10)),REPT(CHAR(10),2),CHAR(10))

If this post helps click Yes

 

[harwookf]

I have just seen that there is a limit on the number of characters for each
cell - 1024 I believe.

Therefore, what I would like to do is only include the data from the other
worksheet if the data in ColumnA on that worksheet equals Q1 2009, for
example.

 

[harwookf]

Thanks for the response. This solution has worked thank you.

The data I have in the cells is text with multiple words - in fact, they are
sentences. As I have noticed there is a limitation on the number of
characters - 1024, I was wondering if the formula could be modified so that
it only shows depending on what is in Column A on the other worksheet.

For example,
A B
Q1 2009 Notes in sentences
Q2 2009 More notes
Q2 2009 Again

So, if column A = Q2 2009, then I would see 'More notes Again' when
concatenated.

Hope this makes sense.

 

[Don Guillett]

How about a nice macro

Sub conem()
With Sheets("Input Notes Sheet")
For i = 2 To 14
If Application.Trim(Len(.Cells(i, "c"))) > 0 Then
ms = ms & .Cells(i, "c") & Chr(10)
End If
Next i
MsgBox ms
Range("k10") = ms
End With
End Sub

 

[Jacob Skaria]

You will have to add conditions for each cell like
concatenate(IF(A1="Q2 2009",B1,""),char(10),IF(A2="Q2
2009",B2,""),...............
which will make the formula even bigger and difficult to edit...

If this post helps click Yes

 

[Don Guillett]

You can modify this to stop at certain point.
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[harwookf]

Don - I have sent the file to you with an explanation which I hope is clear.
Thanks for your help.

Karen