+ Reply to Thread
Results 1 to 3 of 3

Need help to Concatenate multiple cells and inserting a space with comma in between

  1. #1
    Registered User
    Join Date
    05-20-2014
    Posts
    2

    Need help to Concatenate multiple cells and inserting a space with comma in between

    I have several lists of hundreds of emails that I need to list in an email string via Lotus Notes. I need a space and comma in between each name, ex:

    [email protected], [email protected], [email protected]

    I am using the below formula manually but it is taking too long. Can anyone help automate this? Is there a way to create the space and comma for as long as the list is?


    =CONCATENATE(B6,", ",B7,", ",B8,", ",B9,", ",B10,", ",B12,", ",B13,", ",B14,", ",B15,", ",B16,", ",B17)


    Thanks!
    T

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Need help to Concatenate multiple cells and inserting a space with comma in between

    Let's pretend Column C is blank.

    C7:

    =B6&", "&B7

    C8:

    =C7&", "&B8

    now drag C8's formula all the way down to the end of the list

    Copy that last cell, paste values into an empty cell. Voila, long list in one big string.
    Attached Files Attached Files
    Last edited by daffodil11; 05-23-2014 at 05:47 PM.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need help to Concatenate multiple cells and inserting a space with comma in between

    Here's a link to a really good VBA function that'll do this:

    http://www.excelforum.com/showthread.php?p=3096647

    Install the function then use it like this:

    Data Range
    B
    C
    5
    6
    Email1
    Email1, Email2, Email3, Email4, Email5
    7
    Email2
    8
    Email3
    9
    Email4
    10
    Email5
    11


    This formula entered in C6:

    =concatall(B6:B10,", ")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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. [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
  2. Replies: 7
    Last Post: 02-10-2013, 02:10 AM
  3. [SOLVED] Macro inserting comma & space
    By deficit in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-30-2012, 11:05 AM
  4. Macro inserting comma & space
    By katmison in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-29-2012, 10:24 PM
  5. Add Space between concatenate cells
    By Mac Landers in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-01-2005, 05:06 PM

Tags for this Thread

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