# Concatenate Formula Help

#### squeakums

##### Well-known Member
Okay, is there a way to write a concatenate formula that will do the following:

For Example:
Take these names and place them in the following format:

Column A
WI Burt Reynolds
WI Kate Moses
WI Happy Gilmore
WI Tracey Dave
WI Jodi Dawn

("WI Burt Reynolds", "WI Kate Moses", "WI Happy Gilmore", "WI Tracey Dave", "WI Jodi Dawn")

Or, is there a macro that can do this? I need help.

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### Jonmo1

##### MrExcel MVP
Perhpas...

=A1&", "&A2&", "&A3&", "&A4&", "&A5

#### squeakums

##### Well-known Member
Nope, that doesn't work. Or, perhaps a formula that can do this?

#### VoG

##### Legend
Try this UDF

Code:
Function Mrg(r As Range)
Application.Volatile
Dim c As Range, m As String
For Each c In r
If c.Value <> "" Then m = m & c.Value & ", "
Next c
If m = "" Then
Mrg = "All blank"
Else
Mrg = Left(m, Len(m) - 2)
End If
End Function

<b>Sheet4</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:146px;" /><col style="width:37px;" /><col style="width:537px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >WI Burt Reynolds</td><td > </td><td >WI Burt Reynolds, WI Kate Moses, WI Happy Gilmore, WI Tracey Dave, WI Jodi Dawn</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >WI Kate Moses</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >WI Happy Gilmore</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >WI Tracey Dave</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >WI Jodi Dawn</td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C1</td><td >=mrg(A1:A5)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

#### squeakums

##### Well-known Member
Almost works, except needs to be in format ("WI Adam Kast", "WI Beer Buds")

etc.

#### VoG

##### Legend
Try

Code:
Function Mrg(r As Range)
Application.Volatile
Dim c As Range, m As String
For Each c In r
If c.Value <> "" Then m = m & Chr(34) & c.Value & Chr(34) & ", "
Next c
If m = "" Then
Mrg = [#N/A]
Else
Mrg = Left(m, Len(m) - 2)
End If
End Function

#### EB08

##### Active Member
cut that...copied wrong

Copied what you wanted it to look like in a cell to try and match it...thought i matched it but didnt...copied the original you posted back in here

Last edited:

#### EB08

##### Active Member
Now, This worked for me...Let me know how it goes.

=IF(COUNTA(A1:A5),SUBSTITUTE(TRIM(A1&", "&A2&", "&A3&", "&A4&", "&A5)," "," "))

you adjust the range as you see fit...

General Concept credit goes to "Erik"...I remember seeing something like this from one of his previous posts.

#### squeakums

##### Well-known Member
The second formula posted works. Thanks!

Replies
5
Views
328
Replies
5
Views
296
Replies
13
Views
647
Replies
3
Views
206
Replies
1
Views
290

1,190,913
Messages
5,983,536
Members
439,848
Latest member
timmyo

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