+ Reply to Thread
Results 1 to 4 of 4

Concatenate thousands of cells with a comma between each one?

  1. #1
    Forum Contributor
    Join Date
    05-21-2014
    Location
    United States
    MS-Off Ver
    Office 2007 on Windows 7
    Posts
    118

    Question Concatenate thousands of cells with a comma between each one?

    I have over 6,000 cells in a column that I need to join together with a comma (no spaces) between each one. Any easy ways of doing this?

    I tried following these instructions, but it tells me that I am over the character limit once I press F9.

    Select cell B2.
    Type =A1:A6282&", " in formula bar.
    Press F9.
    Delete curly brackets in formula bar.
    Delete last delimiting character.
    Type =Concatenate( in front of all characters in formula bar.
    Type ) after last character in formula bar.
    Press Enter

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,714

    Re: Concatenate thousands of cells with a comma between each one?

    Cell character limit is 32K so (not knowing the length of each cell) I guess you are over that.

  3. #3
    Registered User
    Join Date
    12-22-2015
    Location
    Huntsville, AL
    MS-Off Ver
    2007
    Posts
    92

    Re: Concatenate thousands of cells with a comma between each one?

    MetroBOS,

    I've used the method outlined here several times. http://chandoo.org/wp/2014/01/13/com...ues-quick-tip/

    I think it should help you as well (it even gives you an alternate to using F9.

  4. #4
    Registered User
    Join Date
    06-03-2013
    Location
    NSW
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Concatenate thousands of cells with a comma between each one?

    MetroBOS,

    Do you have a solution...? If so, please mark this as Solved. If not, can you better explain your problem?

    Do you mean that you are trying to take data from each cell in Column A1 (xlDown), then 'transpose' that entire column of data such that each value is joined with each 'other' value (the next cell down) with a comma?

    Something like this;
    A1 | A2 | A3
    Val1
    Val2
    Val3
    Val4

    ....changed to this;

    Val1,Val2,Val3,Val4........Valn

    If so, you can simply use Concatenate in a rigid formula such as this;
    In say Cell A2, type
    =A1&","&A2&","&A3&","&A4&"," etc. etc. to &An&

    Similarly, you can record a macro to achieve the same thing if you really have 6000 items in a coulmn to do.

    H.
    --If You Can't Measure It, You Can't Manage It--

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 9
    Last Post: 04-09-2018, 09:46 PM
  2. Changing decimal and thousands from a comma to a period
    By djmatok in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-29-2015, 11:52 AM
  3. Replies: 2
    Last Post: 05-23-2014, 06:49 PM
  4. [SOLVED] CONCATENATE Multiple Cells into One with Comma
    By Karen615 in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-16-2013, 03:30 PM
  5. Syntax for formatting a number with a comma for thousands
    By HangMan in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-30-2013, 06:01 PM
  6. [SOLVED] Need a Macro / VBA to find numbers in a text string and add a thousands comma
    By chicolocal in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-27-2012, 03:12 PM
  7. [SOLVED] thousands comma separator in VBA
    By Wendy Francis in forum Excel General
    Replies: 2
    Last Post: 10-24-2005, 09:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1