|
Concatenate problem
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)) |
Concatenate problem
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),C HAR(10)) If this post helps click Yes --------------- Jacob Skaria "harwookf" wrote: 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)) |
Concatenate problem
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" wrote: 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)) |
Concatenate problem
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. "Jacob Skaria" wrote: 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),C HAR(10)) If this post helps click Yes --------------- Jacob Skaria "harwookf" wrote: 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)) |
Concatenate problem
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 --------------- Jacob Skaria "harwookf" wrote: 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" wrote: 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)) |
Concatenate problem
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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "harwookf" wrote in message ... 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)) |
Concatenate problem
Don - I have sent the file to you with an explanation which I hope is clear.
Thanks for your help. Karen "Don Guillett" wrote: 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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "harwookf" wrote in message ... 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)) |
| All times are GMT +1. The time now is 11:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com