Excel: concatenate data into 1 cell

Experts,  

Can someone show me how to take a range of data and insert it into 1 cell.  I would like to have it seperate the words with a comma and a space..  Is this possible?

Please see attached. Data.xlsm
Maliki HassaniAsked:
Who is Participating?
 
redrumkevConnect With a Mentor Commented:
LANCE S P

How are you doing?

I inserted a function, see code and workbook below.

The function is called by the following:

=CONCAT1(cell:cell) the range of cells you want.

In your case: =CONCAT1(A1:A429)

You can add/change the delimited by putting it after the range such as:

=CONCAT1(A1:A429,",")

I placed the above in cell C3

Hope this helps,
Kevin

Function Concat1(myRange As Range, Optional myDelimiter As String)
    Dim r As Range

    Application.Volatile
    For Each r In myRange
        Concat1 = Concat1 & r & myDelimiter
    Next r
    If Len(myDelimiter) > 0 Then
        Concat1 = Left(Concat1, Len(Concat1) - Len(myDelimiter))
    End If
End Function

Open in new window

Data.xlsm
0
 
redrumkevCommented:
LANCE S P,

Reference for the above:

http://excel.tips.net/Pages/T003062_Concatenating_Ranges_of_Cells.html

Regards,
Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
The easiest way is to copy the cells into a text editor, change the carriage returns to spaces, copy the resulting text, and paste into the desired cell in your workbook.

Kevin
0
 
nithaiCommented:
First Of all you will have to get the MoreFunc.dll for excel. Here is the link for it

http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

 Once you install it, there is a  "MCONCAT()" Function . This will help you concatenate a range of cells and you can use your desired separator.

eg : =MCONCAT(A1:A10,",")

Hope this solves your problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.