1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

[HOW]Excel column to 1 cell

Discussion in 'BlackHat Lounge' started by robibrk, Mar 20, 2012.

  1. robibrk

    robibrk Regular Member

    Joined:
    Aug 23, 2009
    Messages:
    321
    Likes Received:
    121
    I have an excel file with one column A (800 lines)- 1 keyword per line. I want join all this keywords to one cell (i know, it will be a large one:D) each KW trimmed with , or |.

    Is there any way for this?
     
    Last edited: Mar 20, 2012
  2. necro

    necro Regular Member

    Joined:
    Dec 23, 2010
    Messages:
    292
    Likes Received:
    189
    copy to editor and then back to excel...
     
  3. artizhay

    artizhay BANNED BANNED

    Joined:
    Nov 21, 2010
    Messages:
    1,867
    Likes Received:
    1,335
    Code:
    Function CombineColumn(myRange As Range)
    
        Dim cell As Range
        For Each cell In myRange
            If Len(cell) > 0 Then
                CombineColumn = CombineColumn & cell.Value & ","
            End If
        Next cell
        CombineColumn = Left(CombineColumn, Len(CombineColumn) - 1)
        
    End Function
    Write formula in cell:
    Code:
    =CombineColumn(A1:A800)
    etc.
     
    • Thanks Thanks x 1
  4. honzarox

    honzarox Junior Member

    Joined:
    Feb 28, 2008
    Messages:
    131
    Likes Received:
    52
    =A1&", "&A2&", "&AZ

    This will give you A1[comma][space]A2[comma][space]AZ
     
    • Thanks Thanks x 1
  5. robibrk

    robibrk Regular Member

    Joined:
    Aug 23, 2009
    Messages:
    321
    Likes Received:
    121
    Thanks guys! +rep added