[HOW]Excel column to 1 cell

robibrk

Regular Member

Aug 23, 2009

321

121

• Mar 20, 2012

• #1

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) each KW trimmed with , or |.

Is there any way for this?

 

Last edited: Mar 20, 2012

necro

Regular Member

Dec 23, 2010

313

194

• Mar 20, 2012

• #2

copy to editor and then back to excel...

 

artizhay

BANNED

Nov 21, 2010

1,867

1,340

• Mar 20, 2012

• #3

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.

 

H

honzarox

Junior Member

Feb 28, 2008

133

52

• Mar 20, 2012

• #4

=A1&", "&A2&", "&AZ

This will give you A1[comma][space]A2[comma][space]AZ

 

robibrk

Regular Member

Aug 23, 2009

321

121

• Mar 20, 2012
• Thread Starter Thread Starter

• #5

Thanks guys! +rep added