Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel:  concatenate data into 1 cell

Posted on 2011-03-02
4
Medium Priority
?
350 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:Maliki Hassani
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 23

Accepted Solution

by:
redrumkev earned 2000 total points
ID: 35022042
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
 
LVL 23

Expert Comment

by:redrumkev
ID: 35022053
LANCE S P,

Reference for the above:

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

Regards,
Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35022057
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
 

Expert Comment

by:nithai
ID: 35022114
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question