Consolidate text cells into one cell - seperated by comma

[nikegeo]

Hey Everyone,

as the topic says im trying to consolidate a # of cells (text values) into 1 cell seperated by a ",_" (underscore = space)

i have over 53 cells that i'd like to go from

<table border="0" cellpadding="0" cellspacing="0" width="244"><col style="mso-width-source:userset;mso-width-alt:8923;width:183pt" width="244"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:183pt" height="20" width="244">Argentina</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">Canada</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">Colombia</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">Costa Rica</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">Dominican Republic</td> </tr> </tbody></table>
to

Argentina, Canada, Colombia, Costa Rica, Dominican Republic - All in one cell

I know i can concatenate it - =CONCATENATE(A9,", ",A10,", ",A11,", ",A12,", ",A13,", ", - but would love to avoid having to click 50+ cells and paste the ", ", each time

Anyway to make this easy?

thanks in advance

 

[VoG]

Try this UDF in a standard module

Function aconcat(r As Range) As String
aconcat = Join(Application.Transpose(r), ",_")
End Function

To use for example

=aconcat(A1:A50)

 

[nikegeo]

Thanks for the quick response!

I'm not sure if im doing this correctly (Novice @ VB) - I created a new module in my personal.xlsb (macro workbook) and pasted code - saved - but the function is not recognized in my other workbook?

 

[VoG]

Try

=personal.xlsb!aconcat(A1:A50)

 

[nikegeo]

fantastic it worked!

Anyway to make this a global function and have it in other spreadsheets?

Thanks again and you and this forum make my job so much more easier!

 

[VoG]

As you have it in your personal.xlsb then it should be available in any workbook.