Concatenate Formula Help

squeakums

Well-known Member
Joined
May 15, 2007
Messages
823
Office Version
  1. 365
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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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
 
Upvote 0
Almost works, except needs to be in format ("WI Adam Kast", "WI Beer Buds")

etc.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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
Back
Top