Concatenate Formula Help

S

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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
VoG

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
S

squeakums

Well-known Member
Joined
May 15, 2007
Messages
823
Office Version
  1. 365
Almost works, except needs to be in format ("WI Adam Kast", "WI Beer Buds")

etc.
 
Upvote 0
VoG

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
E

EB08

Active Member
Joined
Jun 9, 2008
Messages
343
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
E

EB08

Active Member
Joined
Jun 9, 2008
Messages
343
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
You must log in or register to reply here.

Similar threads

T
  • Solved
Making a tough Excel Formula take one input as a range, and output as a Spill Array/Range.
Replies
5
Views
328
Fluff
Fluff
L
  • Question
Macro formula to Replicate VBA Recording from one tab to all other tabs
Replies
5
Views
296
Lowbob131
L
R
  • Question
CUT AND PASTE (FORMULA WITH CONCATENATE)?
2
Replies
13
Views
647
Peter_SSs
Peter_SSs
J
  • Question
Macro to Copy and Paste into cell, as a value and based on colour?
Replies
3
Views
206
Micron
M
F
  • Question
IF Formula Creating Hyperlink In More Cases Than I Want
Replies
1
Views
290
FrankNJohnson
F

Forum statistics

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

Share this page

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
Top