Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    May 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    combining data from one column all into one cell

    Okay, I have a list of exported emails (like over 2000+ addresses) all in column A;

    I need to have them all combined into one cell and separated by a comma.

    HELP!

    I can do the =A1&","&A2&","&A3& (etc) command, but then I would have to add all the A(+row number) all the way up to 2046!! There must be a better way to tell it to do a range of cells???

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    NOTthepro,

    I think that even though you could do this with some VBA code, and maybe by a clever formula, it isn't practical as the limit for the number of characters in a single cell is 32767 if you divide that by 2000 that leaves only 16.3835 characters per email address + the comma. Don't think you'll get them all in there. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    May 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    (sigh)

    Well even if I could break it up into 10 different, or even 50 different cells ~ It would be better than having to do it manually!

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    I posted a file here previously which used a custom function to concatenate a range of cells.
    This function can also be used to ensure that any items (i.e. cell contents) joined are unique.

    I have attached the file here.
    You could use it to merge 'chunks' of cells.
    And you don't need anything like
    =A1&","&A2&","&A3& (etc) command
    Instead, you use something like this:
    D1 = myCONCAT(A1:A200)
    D2 = myCONCAT(A201:A400)
    D3 = myCONCAT(A401:A600)
    etc etc.

    let me know if this works for you.

    zeddy
    Attached Files Attached Files

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    Gee how did I know you'd come up with a custom function.

    In case NOTthepro is interested here's a solution using VBA and allows you to somewhat determine how many characters are in each group.
    Code:
    Option Explicit
    
    Sub CombineAddrs()
    
       Dim shtBase     As Worksheet
       Dim shtCombined As Worksheet
       Dim lCntr       As Long
       Dim zTempStr    As String
       
       Application.ScreenUpdating = False  'Keep screen from flashing
       
       Set shtBase = Sheets("Base")
       Set shtCombined = Sheets("Combined")
       
       shtCombined.Activate
       ActiveCell.CurrentRegion.ClearContents 'Clear Previous Runs
       [a1].Select
       
       shtBase.Activate
       
       lCntr = 1
       
       Do While Cells(lCntr, 1).Value <> ""
       
         zTempStr = zTempStr & Cells(lCntr, 1).Value
         
         If Len(zTempStr) > 2000 Then  'Limit string to 20000 chars...up to 32000 if desired.
           shtCombined.Select
           ActiveCell.Value = zTempStr    'Store temporary string in cell.
           zTempStr = ""                  'Empty temporary string
           ActiveCell.Offset(1, 0).Select 'Move to next cell
           shtBase.Select
         Else
           zTempStr = zTempStr & ","      'Add comma to end of temporary string
         End If
         
         lCntr = lCntr + 1
         
       Loop
       
       'Process remaining data in ztempstr
       zTempStr = Left(zTempStr, Len(zTempStr) - 1) 'Remove trailing comma
       shtCombined.Select
       ActiveCell.Value = zTempStr    'Store temporary string in cell.
       zTempStr = ""                  'Empty temporary string
       shtBase.Select
       
       MsgBox "Processed: " & Format(lCntr - 1) & " Records", _
              vbOKOnly + vbInformation, "Status Message"
    End Sub
    HTH

    VBA - Excel - NOTthePro.xlsm
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    New Lounger
    Join Date
    May 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I get it.... but I cannot make it work!!! This is exactly what I need/ want ~ (sigh) I get a name error / function error.

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi NOTthe pro

    Which one are you talking about???

    If you are using your own file, you need to copy my custom Function module into your file.
    To quickly copy the module into your file:
    Open both files.
    Press [Alt][F11] to go to vb editor window.
    Press [Ctrl][R] to view the left-hand pane Project Explorer.
    Look for item modFunctions
    Point to this and Drag it (up or down) to your File (your file will appear as VBAProject(yourfilename.xls)
    Press [Alt][F11] to switch back to your Excel window.
    Now use the function.

    OR:
    Copy your data into my file, say on sheet 2, then use my function to merge your selected cell range.

    zeddy

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi RG

    Nifty vba. Nice. I like it.

    But my custom function is perhaps a little more versatile??
    ..If required, it can sort, use different separator characters, work with multiple source columns and rows, and remove duplicates.

    zeddy

  9. #9
    New Lounger
    Join Date
    May 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    you are so Awsoommmmmmme!!!!!! It worked perfectly~ saved me so much time!! Thank you thank you!!!

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Which one did you use. Was it RG or zeddy??????
    Or was it one each on the thank you thank you.

    z

Posting Permissions

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