variable length concatenation.

EDUCATED MONKEY

Board Regular
Joined
Jul 17, 2011
Messages
218
Win XP Professional office 2003 IE 8<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
Hello I am looking for some help creating a variable length concatenation.<o:p></o:p>
<o:p> </o:p>
All the data is in column A, the start point will be Row A6 and can extend down an unknown amount of rows, the clue as to were the concatenation ends will be the word date.<o:p></o:p>
<o:p> </o:p>
e.g.<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
row 1 some text<o:p></o:p>
row 2 some text<o:p></o:p>
row 3 some text<o:p></o:p>
row 4 some text<o:p></o:p>
row 5 some text<o:p></o:p>
row 6 start of concatination down to row X -1<o:p></o:p>
concatination <o:p></o:p>
concatination<o:p></o:p>
concatination <o:p></o:p>
concatination<o:p></o:p>
concatination <o:p></o:p>
concatination<o:p></o:p>
row x containing the word “Date”<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
N.B row 6 is where I need the complete string to end up <o:p></o:p>
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,718
Office Version
  1. 2010
Platform
  1. Windows
Give the macro a try...

Code:
Sub ConCatA7ToDateCell()
  Dim DateCell As Range
  Set DateCell = Range("A7:A" & Cells(Rows.Count, "A").End(xlUp).Row).Find("Date", , xlValues, xlPart, xlByColumns, xlNext, False)
  If Not DateCell Is Nothing Then
    Range("A6").Value = Join(WorksheetFunction.Transpose(Range("A7:A" & (DateCell.Row - 1))))
  End If
End Sub
Note: You didn't say, so I assumed you wanted a space between each cell's text.
 

EDUCATED MONKEY

Board Regular
Joined
Jul 17, 2011
Messages
218
Give the macro a try...

Code:
Sub ConCatA7ToDateCell()
  Dim DateCell As Range
  Set DateCell = Range("A7:A" & Cells(Rows.Count, "A").End(xlUp).Row).Find("Date", , xlValues, xlPart, xlByColumns, xlNext, False)
  If Not DateCell Is Nothing Then
    Range("A6").Value = Join(WorksheetFunction.Transpose(Range("A7:A" & (DateCell.Row - 1))))
  End If
End Sub
Note: You didn't say, so I assumed you wanted a space between each cell's text.
Hi thanks for the code, I am having some difficulty in get it to work below is the data I tried to no avail, I have walked through your code and it seems reasonable and I have no idea why it has fail to do the task, I was expecting a concatenated string in cell A6 of the two paragraphs but got nothing <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Can you see my error <o:p></o:p>
<o:p></o:p>
<TABLE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; MARGIN: auto auto auto 42pt; WIDTH: 416.5pt; BORDER-COLLAPSE: collapse; BORDER-TOP: medium none; BORDER-RIGHT: medium none; mso-border-alt: solid windowtext .5pt; mso-yfti-tbllook: 480; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-border-insideh: .5pt solid windowtext; mso-border-insidev: .5pt solid windowtext" class=MsoTableGrid border=1 cellSpacing=0 cellPadding=0 width=555><TBODY><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 0"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt" vAlign=top width=555>title: Percy's Park - After the Storm <o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555>author: Nick Butterworth <o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555>format: Paperback <o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555>isbn: 0-00-100448-4 / 978-0-00-100448-1 <o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555>publisher: HarperCollins <o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 5"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555><o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 6"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555><o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 7"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555><o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 8"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555><o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 9"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555><o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 10"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555><o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 11"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555><o:p></o:p>

</TD></TR><TR style="HEIGHT: 46.15pt; mso-yfti-irow: 12"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 46.15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555>A storm is raging through the park causing Percy, the park keeper, to have a sleepless night worrying about the damage being done to his beloved park. When he gets up the next morning he finds the old oak tree has been blown down. This is a disaster for all his animal friends who used the tree as their home. Fortunately, Percy is on hand to help find them a new place to live. <o:p></o:p>

</TD></TR><TR style="HEIGHT: 34.8pt; mso-yfti-irow: 13"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 34.8pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555>The simple text and expressive illustrations perfectly combine to create a picture storybook that is ideal for reading aloud to young children. The poster-sized gatefold at the end is an added surprise and is a superb example of Nick Butterworth's skills as an illustrator. (Ages <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:eek:ffice:smarttags" /><st1:time Minute="58" Hour="5">2 to 6</st1:time>) <o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 14"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555><o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 15"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555>Date<o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 16"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555>Search for Author Book Short story ISBN <o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 17; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555><o:p></o:p>

</TD></TR></TBODY></TABLE>

There error pointed to this line of code

Range("A6").Value = Join(WorksheetFunction.Transpose(Range("A7:A" & (DateCell.row - 1))))
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,718
Office Version
  1. 2010
Platform
  1. Windows
Hi thanks for the code, I am having some difficulty in get it to work below is the data I tried to no avail, I have walked through your code and it seems reasonable and I have no idea why it has fail to do the task, I was expecting a concatenated string in cell A6 of the two paragraphs but got nothing <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Can you see my error
There error pointed to this line of code

Range("A6").Value = Join(WorksheetFunction.Transpose(Range("A7:A" & (DateCell.row - 1))))
A couple of questions. What did the error message say? Are there any merged cells in Column A?
 

EDUCATED MONKEY

Board Regular
Joined
Jul 17, 2011
Messages
218

ADVERTISEMENT

Run time error -214741848(80010108)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
Method transpose of object worksheet function failed<o:p></o:p>
as far as i know there are no merged celll as the data gets pasted in to column A1
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,718
Office Version
  1. 2010
Platform
  1. Windows
I think we may be running into some kind of text length limit. For your posted example, they are both more than 255 characters in length. If we reduce each of them to a length equal to or less than this number, the code works fine. It appears the Transpose function has a 255 character limit on the text in the cells being transposed. It looks like we will have to use the brute-force method of concatenating your text...

Code:
Sub ConCatA7ToDateCell()
  Dim X As Long, Text As String
  For X = 7 To Cells(Rows.Count, "A").End(xlUp).Row
    Text = Text & " " & Cells(X, "A").Value
  Next
  Range("A6").Value = WorksheetFunction.Trim(Text)
End Sub
 

EDUCATED MONKEY

Board Regular
Joined
Jul 17, 2011
Messages
218
Yes i have been trying to do some thing along those lines, first i used match to find the row the retrieve that value inside the sub to form a loop untill the cell has be check to see if it's blank the concatenating them not had much luck doing yet, its a good job i tried your code otherwise i would not of knowen about the 255 limit and drove my self mad tring to fix it, at least i know the range is now A6 to N, Nbeing the value passed to the sub, if you have any other ideas i would like to try then thanks for your help
 

Watch MrExcel Video

Forum statistics

Threads
1,123,427
Messages
5,601,605
Members
414,462
Latest member
StageRiis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top