CONCATENATE Question

[ptaylor]

I have a list of emails in a column and want to put them into a single cell so I can send out a blast email. How would I go about doing this where I do not have to manually enter CONCATENATE(A2,",",A3...etc)?

Thanks

 

[Kaps_mr2]

A1 & "," & A2 & "," & "A3" & "," & "A4" .....


Kaps

 

[Colin Legg]

Say your list of addresses is in A1:A20.

Put this formula in B2:

=A2

Then put this formula in B3:

=B2&" , "&A3

And copy that formula down the B column to the end of the list.

 

[e18cincy]

I like that last solution. If you want something that doesn't show up on the page, i.e. code, this should work, assuming your list is in column 3:

Sub Concatenator()
'
Dim i, j As Integer
Dim k As String
i = 1
j = 1
Do While Cells(i, 3) <> ""
i = i + 1
Loop
k = "=Concatenate("
Do While j < i
If j = 1 Then
k = k & Cells(j, 3).Address
Else
k = k & ","",""" & "," & Cells(j, 3).Address
End If
j = j + 1
Loop
Cells(i, 3).Value = k & ")"
End Sub

 

[Scott Huish]

With your addresses starting in A1, this will put the concatenated list one row below your last e-mail address.

Sub test()
Dim c As Range, x As Long, t As String
x = Range("A" & Rows.Count).End(xlUp).Row
For Each c In Range("A1:A" & x)
    t = t & c & ","
Next
Range("A" & x + 1) = Left(t, Len(t) - 1)
End Sub

If your addresses start in row 2 change this line:

For Each c In Range("A1:A" & x)

to

For Each c In Range("A2:A" & x)

 

[wiwchar]

I am also trying to concatenate a list on a spreadsheet. The way it is set up, is that in column A there is a sequence number. In column B there is a part number. I want to concatenate K plus the right 3 characters of the part number for as many part numbers as the sequence number contains. Each sequence number has a different number and combination of part numbers.

Column A Column B
12045 D-055
D-058
D-059
D-051
12046 D-058
D-060
12047 D-058
D-051
D-059

The results that I would get after the concatenate would be K055058059051, K058060, K058051059. Manually I can do this using concatenate() and right(), but I have about 1600 sequence numbers to work with. As you can see, the sequence number is not listed again until it is changed.

Is there a solution?

Thanks in advance.

 

[e18cincy]

This assumes your two columns of data are in columns A and B and you want the concatenated strings in column C. In addition, it assumes that there is a sequence number for each one in Column A. If that's not the case, please let me know. Try this code:

Sub Concatenation()
i = 1
Do While Cells(i, 2) <> ""
Cells(i, 3) = CStr(Cells(i, 1).Value) & Right(Cells(i, 2), 3)
i = i + 1
Loop
End Sub

 

[Scott Huish]

In addition, it assumes that there is a sequence number for each one in Column A. If that's not the case, please let me know.

The op has already said this is not the case:

As you can see, the sequence number is not listed again until it is changed.

Your code does not include the K, but the op wants to have a K then the right 3 numbers for every value of the same sequence until it changes not individual ones per line, according to the sample result the op gave, the first being K055058059051

 

[Scott Huish]

I think this will do it, it puts the concatenated value at the end of each section in Column C, the code assumes data starts in Row 1, change as necessary:

Sub Concatenation()
Dim x As Long, tmp As String, c As Range, cv, nv
x = Range("B" & Rows.Count).End(xlUp).Row
For Each c In Range("B1:B" & x)
    cv = c.Offset(, -1)
    nv = c.Offset(1, -1)
    If IsNumeric(cv) And cv <> "" Then
        tmp = "K" & Right(c, 3)
    Else
        tmp = tmp & Right(c, 3)
    End If
    If (IsNumeric(nv) And nv <> "") Or c.Row = x Then
        c.Offset(, 1) = tmp
        tmp = ""
    End If
Next
End Sub

 

[wiwchar]

Thank you! This last one provided the string I require. Would it be possible to put the string at the beginning of each section as opposed to the end as it is now?