Solved: Concatenation

[danlu]

Hi,

I have a column with about 500-1000 values where I want to concatenate all these values into one cell with a ";" between each value (and the function =concatenate() could be used but is quite time consuming....).

So what I hope for is a sub that will do the trick, that is go from the top of a column and downwards and concatenate all cells with values (non blanks) down to the first blank cell, and then paste this string of values separated by a semicolon into one single cell.
Any ideas?

[johnske]

Option Explicit

Sub CatenateIt()
'replace A1:G2 below with your own range
'DEMO: concatenate A1 to A200
Range("H1") = Catenate(Range("A1:A200"), ";")
End Sub

'<< Function to concatenate cells in a range >>
Public Function Catenate(MyRange As Range, _
Optional Delimiter As String) As String
Dim Cell As Range, N As Long
N = 1
'go thru MyRange cell by cell and concatenate
For Each Cell In MyRange
If N = MyRange.Cells.Count Then
'we don't need a delimiter after last cell
Catenate = Catenate & Cell
Else
'otherwise we do need a delimiter
Catenate = Catenate & Cell & Delimiter
End If
N = N + 1
Next Cell
Set Cell = Nothing
End Function

[RichardSchollar]

Hi

An alternative function:

Function JoinCol(ByVal rng As Range) As String
Dim myArray
myArray = rng
myArray = Join(Application.WorksheetFunction.Transpose(myArray), ";")
JoinCol = myArray
End Function

You can either call this from a sub or use in the worksheet (note, this only works with single column ranges).

eg

=JoinCol(C1:C10)

Best regards

Richard

[danlu]

Hi Johnske and RichardSchol

I have tried your solutions but not quite managed to get them work
In both cases I recieve the #NAME! or #VALUE! errors.
Maybe it's me who do something wrong.

RichardSchol, with your function I guess it is just to paste it into a module and then on the sheet, choose any cell (except A1 to A200 where my data is) and write =joincol(A1:A200)
What could be the problem?

Johnske, I guess you can use your sub as stand alone (without the function you entered under the sub)? In the sub, doesn't "Catenate" has to be defined in some variable?

[RichardSchollar]

Hi Danlu

I gather you are using Excel97 or below? Join doesn't exist in 97 (and it may not in 2000 - can't remember if this is the case) which would mean my function wouldn't work.

As to Johnske's code, he provided a function because you asked for one in your first post. The actual functional code (ie that performs the concatenation) resides within the Function. So, if you have pasted in the function code to a module, you can use Johnske's code in much the same way you were trying to use mine (but his won't fail). So:

=Catenate(a1:a200,";")

should work for you.

Richard

[Bob Phillips]

I gather you are using Excel97 or below? Join doesn't exist in 97 (and it may not in 2000 - can't remember if this is the case) which would mean my function wouldn't work.

Of course it will.



Function JoinCol(ByVal rng As Range) As String
Dim myArray
myArray = rng
myArray = Join(Application.WorksheetFunction.Transpose(myArray), ";")
JoinCol = Left(myArray, Len(myArray) - 1)
End Function

#If VBA6 Then
#Else
Function Join(sArray, Optional sDelimiter As String = ",")
Dim i As Long
Dim tmp As String
On Error GoTo exit_Join
If sDelimiter = vbNullChar Then sDelimiter = vbNullString
If IsArray(sArray) Then
For i = LBound(sArray) To UBound(sArray)
tmp = tmp & sArray(i) & sDelimiter
Next
If sDelimiter <> vbNullString Then
tmp = Left(tmp, Len(tmp) - 1)
End If
Else
tmp = sArray
End If
exit_Join:
Join = tmp
End Function

#End If

[RichardSchollar]

Of course it will.



Smarta55!!!

Like the code :-)


:bow:

[johnske]

Function JoinCol(MyRange As Range) As String
JoinCol = Join(WorksheetFunction.Transpose(MyRange), ";")
End Function :devil2:

[Bob Phillips]

Uh? Isn't that what Richard said initially?

[johnske]

Yeh, but the 'transpose' puts it into an array - no need to make a new one. :)

[RichardSchollar]

Now that's short code :-)

Since I'm too lazy to test this myself, does this successfully concatenate strings in cells that exceed 255 characters per cell?

[danlu]

Hi,

Thanks a lot all of you, and thanks RichardSchol for making it a bit clearer. A small question to give me the whole picture, could the sub procedure given by Johnske be used alone (without the function)?

[RichardSchollar]

Not without modification, as it contains no definition of how the concatenation should be achieved. It would be possible to rewrite the sub to include the concatenating code elements, but Johnske didn't do this because it is more flexible to keep it wihin its own function (ie so the Sub can call the function, but also the user can write it as a formula within a cell, or indeed call it from within another Sub or Function).

Make sense?

Richard

[danlu]

ok, so the sub is strictly dependent of the function? The sub's functionality in this case is only to put the result in cell H1?
Did I get you right?

[RichardSchollar]

ok, so the sub is strictly dependent of the function? The sub's functionality in this case is only to put the result in cell H1?
Did I get you right?


Absolutely correct :-D

[danlu]

Thanks for the explanation!
This solution will really make my day easier!