# CONCATENATE Question

#### ptaylor

##### New Member
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

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### Kaps_mr2

##### Well-known Member
A1 & "," & A2 & "," & "A3" & "," & "A4" .....

Kaps

#### Colin Legg

##### MrExcel MVP

Put this formula in B2:
Code:
``=A2``

Then put this formula in B3:
Code:
``=B2&" , "&A3``

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

#### e18cincy

##### New Member
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

##### MrExcel MVP

Code:
``````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``````

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

to

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

#### wiwchar

##### Board Regular
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?

#### e18cincy

##### New Member
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

##### MrExcel MVP
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

##### MrExcel MVP
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:

Code:
``````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

##### Board Regular
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?

Replies
0
Views
131
Replies
0
Views
331
Replies
11
Views
286
Replies
0
Views
123
Replies
12
Views
200

1,196,020
Messages
6,012,902
Members
441,739
Latest member
Jeezer

### 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.

### Which adblocker are you using?

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

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